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

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
August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

Community Blog

Community Blog

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

Upcoming Events

Upcoming Events

Wondering what events you could join or have an event to promote yourself? Check out our Upcoming Events.

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

We are thrilled to announce we will begin running a monthly webinar series named Power BI Dev Camp.

Top Solution Authors
Top Kudoed Authors