PQ - True/False expression used in a filter table expression

[MarkCBB]

Hi there,

I have the below DAX function that needs to calculate SUM of the Values from Last Year to Date, however I am apparently using LASTDATE Incorrectly in the DAX Function.

The Below code is the one with the Error.

LYTD Rands = CALCULATE(SUM(MASTER_SALES[VALUE]),MASTER_SALES[YEAR]=YEAR(TODAY())-1,MASTER_SALES[MONTH]<=MONTH(LASTDATE(MASTER_SALES[DATE])))

This code works however it is not what I want as the Today is not correct in the way out data is collected.

LYTD Rands = CALCULATE(SUM(MASTER_SALES[VALUE]),MASTER_SALES[YEAR]=YEAR(TODAY())-1,MASTER_SALES[MONTH]<=MONTH(TODAY()))

I need to adjust the first DAC Function to take the month of the latest Date.

 

[MarkCBB]

ok, after a bit more digging I found the following which works


This is the one for Last Year To Date

LYTD Rands = CALCULATE([Total Rands],DATEADD(DATESYTD(MASTER_SALES[DATE]),-1,YEAR))

Now this now is for This Year To Date

TYTD Rands = CALCULATE([Total Rands],DATESYTD(MASTER_SALES[DATE]))

The interesting thing is that when I filter (Using a slicer on PBI Desktop) on a year i.e. 2015 the LYTD result goes to blank as expected, however the TYTD gets a very strange result. Can someone explain why? (Just curious ).

 

[akice]

I would definitely recommend having a separate Calendar table with all dates inclusive of the range you are working on. Date functions in Dax work by shifting the dates in the filter context, not by creating a Date list or perform other math on Dates. Nor will the Date functions remove filters on other columns the way you have used it which will impact the Dates returned. Hard to say without seeing your model exactly what is happening. So having a separate Calendar table i think will prove much easier to use. Then you can use the built in time intelligence functions which are easier to use and read.

TYTD Rands = TOTALYTD ( [Total Rands], Calendar[Date] )

LYTD Rands = TOTALYTD ( [Total Rands], SAMEPERIODLASTYEAR( Calendar[Date] ) )

 

[MarkCBB]

That was very helpful, Thank you