Month over Month Realized Cost plus Planned Cost

[Jotten]

I am trying to calculate actual cost plus planned cost for the year.  I have two formulas.  The first calculates my actual cost from Jan-May.  For the 2nd formula I would like to take the Planned cost from June - December.  I would then like to total Actuals from (Jan-May) + Planned cost (June-Dec).

Actuals Formula 

Actual_Cost_Endof Month = CALCULATE (SUM('Actual Cost'[Actual Cost]), FILTER(ALL('Actual Cost'[Date]), TODAY() >=MAX('Actual Cost'[Date]))) ---- This calculation works giving me the results of Jan - May per month (but maybe because I don't have any actuals in my table past May).

Planned Test 1

Future_Forecast = CALCULATE(SUM('Planned Forecast'[Planned Labor Cost]), FILTER(ALL('Planned Forecast'[Allocation Date]),TODAY() < MAX('Planned Forecast'[Allocation Date]))) - This formula only calculates the current days left in June and then July - Dec.  The total column with this formula calculates from Jan - Dec.  I would like just all of June - Dec.

Planned Test 2

Future_Forecast = CALCULATE(CALCUATE(SUM('Planned Forecast'[Planned Labor Cost]), 'Planned Forecast'[Allocation Date] >=TODAY()))) ---- This formula only takes the last couple days in June and then July-Dec.  The Total column calculates the couple days in June through December, which is what I want but I would like it to use all of June.

Can someone please help me with this?

[Ashish_Mathur · Accepted solution]

Hi,

Ideally, you should have a Calendar Table with a column of Dates.  There should be a relationship form the Date column of the Actual and Planned tables to the Date column of the Calendar Table.  In the Calendar Table, create a column to extact months, using =FORMAT(Calendar[Date],"MMMM").  In the visual, drag months from the Calendar Table.  The measure for computing future forecast should be:

=CALCULATE(SUM('Planned Forecast'[Planned Labor Cost]),FILTER(Calendar,Calendar[Date]>=EOMONTH(TODAY(),0)+1))

Hope this helps.

[Jotten]

Ashish,

Thank you so much.  This has been very frustrating to me.  I see now rather than useing the 'Planned Forecast'[Allocation Date] I should have been useing my Calendar[Date].  I did alter this slightly as I wanted to pull in all of June.  

Future_Forecast = CALCULATE(SUM('Planned Forecast'[Planned Labor Cost]), FILTER('Calendar','Calendar'[Date]>=EOMONTH(TODAY(),-1)+1))

Many thanks!

Jodie

[Ashish_Mathur]

You are welcome.