cancel
Showing results for
Did you mean:
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:

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
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

3 REPLIES 3
Highlighted
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.

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

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

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

Announcements

#### August 2020 Community Challenge: Can You Solve These?

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

#### Community Blog

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

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

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

Top Solution Authors
Top Kudoed Authors