cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

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

Capture.PNG

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Microsoft
Microsoft

Re: DAX Variance vs. Budget

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

View solution in original post

3 REPLIES 3
Highlighted
Microsoft
Microsoft

Re: DAX Variance vs. Budget

Hi @SlyTokyo,

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

1.png

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



2.png

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.

3.png

Best Regards,
Angelia

Highlighted
Frequent Visitor

Re: DAX Variance vs. Budget

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.

Highlighted
Microsoft
Microsoft

Re: DAX Variance vs. Budget

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

View solution in original post

Helpful resources

Announcements
Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Power Platform 2020 release wave 2 plan

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors