Forum Discussion
How to calculate Total Variance for January month
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 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 :)