I am sure (hope!) this is going to be a simple one…
Please see attached pbix Prior Year query.pbix (106.8 KB)
I have a requirement to display a matrix with our stock position for Today and for PY (the same day last year).
If I was going to be summarising by Date, then I could simply use a measure like:
Stock PY = CALCULATE([Stock Units], SAMEPERIODLASTYEAR(Dates[Date]))
However I am not outputting Date, therefore the above measure returns the total stock quantity for where a previous year value can be evaluated, eg all but 2018:
I have created a column ‘IsToday’ in my Dates table, which will return ‘Yes’ for today and ‘No’ for any other day and I have tried, without success, to incorporate this into my PY measure, eg:
Stock PY =
CALCULATE([Stock Units], SAMEPERIODLASTYEAR(Dates[Date]), Dates[IsToday] = “Yes”)
Stock PY = CALCULATE([Stock Units], DATEADD(Dates[Date], -1, YEAR), Dates[IsToday] = "Yes")
The only way I have succeeded is to add a Visual level filter of IsToday = Yes:
I would prefer to be able to code this in the Dax, rather than rely on the visual level filter. Can anyone advise of Dax code which would replicate this?