How to calculate Total Variance for January month

[Anonymous]

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

[Anonymous · Accepted 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]

[Phil_Seamark]

Hi @Anonymous

If you post a sample set of your table, we can suggest calculations that will fit your model

[Anonymous]

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

[Anonymous]

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

[Anonymous · Accepted 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]

[Anonymous · Accepted solution]

Yes, I got it, Thank you @Anonymous for your help 🙂

[Phil_Seamark]

HI @Anonymous

Do you have a Date (or Calendar) table in your model connected to this table?  

[Anonymous]

Yes @Phil_Seamark I have Snapshot Date in my table.