Measure to return Prior Year value

DAX DAX Calculations
1

Hi all,

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:

image

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”)

and

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:

image
image629×509 13.3 KB

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?

Thanks

Mark

2

Hi @Mark,

There are many ways to calculate this first example measure branching

Stock PY v2 = 
CALCULATE( [Stock PY],
    FILTER( Dates, Dates[IsToday] = "Yes") 
)

or second an all in one

Stock PY v3 = 
CALCULATE( 
    CALCULATE( [Stock Units],
        DATEADD( Dates[Date], -1, YEAR )
    ),
    FILTER( Dates, Dates[IsToday] = "Yes") 
)
1 Like
3

Hi @Melissa

Thanks for quick response and solution.

I was sure I had tried using measure branching and FILTER, however I now realise I had done so in a variable, which of course will not work :persevere:

Eg:

Stock PY (wrong) =

var UnitsSPLY = CALCULATE([Stock Units], SAMEPERIODLASTYEAR(Dates[Date]))

RETURN

CALCULATE(UnitsSPLY, FILTER(Dates, Dates[IsToday] = "Yes"))

Thanks again :smiley:

Mark