Forum Discussion

SlyTokyo's avatar
SlyTokyo
Frequent Visitor
9 years ago
Solved

DAX Variance vs. Budget

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 especia...
Need Help
  • v-huizhn-msft's avatar
    v-huizhn-msft
    9 years ago

    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

SlyTokyo's avatar
SlyTokyo
Frequent Visitor
9 years ago

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's avatar
v-huizhn-msft
Microsoft Employee
9 years ago

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