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.
Hello,
I am struck at how can we calculate the total variance for January month for a particular project.
I came to know that the total variance for January month = (Current month Variance) - (Baseline Dec month Variance)
I have Variance columns in the table. How can I create calculation part to get the total variance for a particular project?
Help needed, thanks in advance.
Bhavesh
Solved! Go to Solution.
If you do not have a date table, you need to create a date table (Date dimension) and connect your snapshot date to the date in newly created date table.
Then create 3measures
1- YTD Current Month := TOTALYTD(SUM('Project Costs'[Variance]), Date[date])
2- YTD Prior Month := CALCULATE( [YTD Current Month], PREVIOUSMONTH(Date[Date]))
3- MTD (January) := [YTD Current Month] - [YTD Prior Month]
Yes, I got it, Thank you @Anonymous for your help 🙂
Hi @Bhaveshp
If you post a sample set of your table, we can suggest calculations that will fit your model
This is my sample table looks like.
Table name: Project Costs
Columns: Project_Number, Variance, POC, Region, Country, Currency_rate, Variance USD
For the above table 'Project Costs' I am trying to find Total variance and POC so that I will get YTD and MTD values
YTD= Total Variance*POC
MTD= (Total variance current month) - (Total variance previous month)
I have POC values column so I need Total Variance in order to find out YTD and MTD.
total variance for January month = (Current month Variance) - (Baseline Dec month Variance)
Please help me, thanks @Phil_Seamark
Thanks for your response. I tried to calculate Total Variance and YTD values by using the below measures
Total Variance = (SUM('Project Costs'[Variance])-CALCULATE(SUM('Project Costs'[Variance]), PREVIOUSMONTH('Project Costs'[Snapshot Date].[Date])))*-1
YTD (January) = YTD Savings Cost reduction(+) / Cost increase(-) = ([Total Variance] * AVERAGE('Project Costs'[POC]))/100
But I am unable to find out MTD values for the table.
MTD (January) = YTD (January) - YTD (December), this is the calculation.
But how can measure MTD values in Power BI report? Please help me, thanks
Bhavesh
If you do not have a date table, you need to create a date table (Date dimension) and connect your snapshot date to the date in newly created date table.
Then create 3measures
1- YTD Current Month := TOTALYTD(SUM('Project Costs'[Variance]), Date[date])
2- YTD Prior Month := CALCULATE( [YTD Current Month], PREVIOUSMONTH(Date[Date]))
3- MTD (January) := [YTD Current Month] - [YTD Prior Month]
Yes, I got it, Thank you @Anonymous for your help 🙂
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 |
---|---|
114 | |
99 | |
83 | |
70 | |
60 |
User | Count |
---|---|
150 | |
115 | |
104 | |
89 | |
65 |