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

v-huizhn-msft's avatar
v-huizhn-msft
Microsoft Employee
9 years ago

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'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