Dynamic Period variance with DAX in #PowerBI

One of those days where I spent a reasonable amount of time trying to solve a tricky DAX expression and once I got it; I came to know it was not at all tricky. In fact, it was far too simple to admit the time spent 🙂

When I showed PARALLELPERIOD and SAMEPERIODLASTYEAR measures to my business user, he asked me if he can select random quarters and measure should always get the variance from the earlier period based on the slicer choice than Time intelligence.

This is how my data looked like:

To get the Selected Previous Period value, I first created a variable to get the Current period value

VAR CurrentPeriod = MAX(Table1[Period])

then I created another table variable to get the selected values from the slicer.  I used CalculatedTable with ALLSelected(), so my context works.

VAR Selectedvalues = CALCULATETABLE(VALUES ( Table1[Period] ),ALLSELECTED(Table1))

Then I created another variable to get the earlier period value based on Filter.

VAR PP=CALCULATE(MAX(Table1[Period]),FILTER(Selectedvalues,Table1[Period]<CurrentPeriod))

And finally, in Return statement, I used the Value column with SUM and filter. Here is the full DAX expression of the measure

Previous Period =

VAR CurrentPeriod = MAX(Table1[Period])

VAR Selectedvalues = CALCULATETABLE(VALUES ( Table1[Period] ),ALLSELECTED(Table1))

VAR PP=CALCULATE(MAX(Table1[Period]),FILTER(Selectedvalues,Table1[Period]<CurrentPeriod))

Return

CALCULATE(SUM(Table1[Val]),Table1[Period]=PP)

If you want to try yourself, here is the M Script for Query Editor.

let

Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCjRU0lEyNFCK1QGyjUBsQwjbGMQ2grBNQGxjpdhYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Period = _t, Val = _t]),

#"Changed Type" = Table.TransformColumnTypes(Source,{{"Period", type text}, {"Val", Int64.Type}})

in

#"Changed Type"

It was a good exercise, I certainly learned how to not to think when working with DAX! Hope it helps someone out there.

URL to download the PBIX – https://1drv.ms/u/s!Avm7gbgZtlMlwFB99Ti5W2_eYsjU

Keep Smiling,

Prathy 🙂