DAX Variance vs. Budget

[SlyTokyo]

Dear All,

I am currently building-up a full P&L automatic analysis.
Last step for me is to integrate variance vs. Budget.
For LY, I used DAX formula (parallel period) which is very nice especially when you are using the dynamic time line on few years historical data.

Below the details of what I am using to do automatic VAR vs. LY in only 3 steps

Step 1) = Vol LY = CALCULATE(sum('HISTORICAL MASTER'[Volume (Tons)]),PARALLELPERIOD('HISTORICAL MASTER'[DATE],-12,MONTH))

Step 2) = Vol = CALCULATE(sum('HISTORICAL MASTER'[Volume (Tons)]),PARALLELPERIOD('HISTORICAL MASTER'[DATE],0,MONTH))

Step 3) = Vol Var % = [Vol]/[Vol LY]-1

I plan to add a column called "sequence" to be able to dissociate Actual & BU and keep DATE

What is the easiest way to build up the VAR vs. BU using DAX formula and keep var vs. LY on the dynamic timeline?

Thank you very much for your support.

Sly

Database is looking like below:

[v-huizhn-msft]

Hi @SlyTokyo,

Do you create measure or column? I try to reproduce it using measure in the following sample data. 



According to three steps, create a measures and create table.

Vol = CALCULATE(sum('HISTORICAL MASTER'[Volumn(Tons)]),PARALLELPERIOD('HISTORICAL MASTER'[DATE],0,MONTH))

Vol LY = CALCULATE(sum('HISTORICAL MASTER'[Volumn(Tons)]),PARALLELPERIOD('HISTORICAL MASTER'[DATE],-12,MONTH))

Vol Var % = [Vol]/[Vol LY]-1

When you have a column sequence, you add the column to table, then it will dissociate Actual & Budget automatically, please see the following screenshot. For instance, 7150=4400+2750.



Best Regards,
Angelia

[SlyTokyo]

Thank you for your feedback @v-huizhn-msft

I found a tricky way to do it by moving around the numbers and keep DAX formula !

It is very close to your suggestion !

Below details:

STEP 1 add a filter Sequence to dissociate BU and actual in 2016 in DAX formula

Vol = CALCULATE(sum('HISTORICAL MASTER'[Volume (Tons)]),PARALLELPERIOD('HISTORICAL MASTER'[DATE],0,MONTH), FILTER('HISTORICAL MASTER', 'HISTORICAL MASTER'[Sequence]="actual"))

Vol BU = CALCULATE(sum('HISTORICAL MASTER'[Volume (Tons)]),PARALLELPERIOD('HISTORICAL MASTER'[DATE],0,MONTH), FILTER('HISTORICAL MASTER', 'HISTORICAL MASTER'[Sequence]="budget"))

STEP 2 calculate var vs. LY for both BU and Actual

 Vol Var = [Vol]-[Vol LY]

 BU LY = [Vol BU]-[Vol LY]

STEP 3 close the loop by doing a Var of Var to get Actual Var vs. BU

Vol Var BU % = ([Vol Var]-[Vol Var BU LY])/[Vol BU]

Best Regards,

Sly.

[v-huizhn-msft · Accepted solution]

Hi @SlyTokyo,

I am very gald to hear that you have resolved your issue, please mark the corresponding reply as answer, which will help more people.

Best Regards,
Angelia