topic Re: Dax not working in DAX Commands and Tips

Dax not working

homelander123 — Wed, 12 Feb 2025 19:03:53 GMT

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

Re: Dax not working

Fowmy — Wed, 12 Feb 2025 19:19:17 GMT

homelander123

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

Re: Dax not working

homelander123 — Wed, 12 Feb 2025 19:27:48 GMT

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

Re: Dax not working

Fowmy — Wed, 12 Feb 2025 19:40:27 GMT

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 )

Re: Dax not working

homelander123 — Wed, 12 Feb 2025 19:43:55 GMT

Fowmy This is not returning any data - Its blank

Re: Dax not working

Anonymous — Mon, 17 Feb 2025 08:19:53 GMT

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.