Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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:
Solved! Go to Solution.
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
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
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.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
104 | |
101 | |
79 | |
72 | |
64 |
User | Count |
---|---|
142 | |
108 | |
101 | |
81 | |
74 |