Dax not working

[homelander123]

PreviousMonthCount = CALCULATE(SUM(Query1[NewCount] ),

FILTER(

    Query1,

    FORMAT(Query1[SetupDate], "yyyyMM") =

    FORMAT(EDATE(SELECTEDVALUE(Query1[SetupDate]),-1),"yyyyMM")

))


I am trying to use this query to show the previous month based on the month selected in the slicer but it is giving me no values in the table

[v-nuoc-msft · Accepted solution]

Hi @homelander123 

Thank you very much Fowmy for your prompt reply.

This is the principle of slicers. When you select a month in a slicer, it will only show data relevant to your selection.

I recommend you create a date table as a slicer, but be careful not to create a relationship between the date table and the original table.

Here's some dummy data

"Query1"

Create a Date table.

Date = 
SELECTCOLUMNS(
    'Query1',
    "Date",
    'Query1'[SetupDate],
    "Year",
    'Query1'[SetupDate].[Year],
    "Month",
    'Query1'[SetupDate].[MonthNo]
)

Select Year and Month from the Date table as slicers.

Create a measure.

PreviousMonthCount = 
VAR SelectYear = SELECTEDVALUE('Date'[Year])
VAR SelectMonth = SELECTEDVALUE('Date'[Month])
VAR PreviousMonthDate = EOMONTH(DATE(SelectYear, SelectMonth, 1), -1)
RETURN
CALCULATE(
    SUM(Query1[NewCount]),
    FILTER(
        ALL('Query1'),
        MONTH('Query1'[SetupDate]) = MONTH(PreviousMonthDate)
        &&
        YEAR('Query1'[SetupDate]) = YEAR(PreviousMonthDate)
        &&
        'Query1'[SetupDate] <= PreviousMonthDate
    )
)

Here is the result.

Regards,

Nono Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[homelander123]

@Fowmy What should be the query for CurrentMonth Last Year based on slicer selection?

[Fowmy]

@homelander123 

Try this:

CurrentMonthLastYearCount = 
VAR SelectedMonth = MAX(Query1[SetupDate])
VAR LastYearSameMonthStart = EOMONTH(SelectedMonth, -12) + 1
VAR LastYearSameMonthEnd = EOMONTH(SelectedMonth, -12)

RETURN 
CALCULATE(
    SUM(Query1[NewCount]),
    Query1[SetupDate] >= LastYearSameMonthStart && Query1[SetupDate] <= LastYearSameMonthEnd
)

[homelander123]

@Fowmy  This is not returning any data - Its blank 

[Fowmy]

@homelander123 

PreviousMonthCount = 
VAR SelectedMonth = MAX(Query1[SetupDate])
RETURN 
CALCULATE(
    SUM(Query1[NewCount]),
    Query1[SetupDate] >= EOMONTH(SelectedMonth, -2) + 1 && Query1[SetupDate] <= EOMONTH(SelectedMonth, -1)
)