Search code examples
exceldaxpowerpivot

DAX/Power Pivot: Calculate 75% Expended Date from Cumulative Total

I have three tables that contain cost: Forecasted Cost, Actual Costs, Invoiced Costs. Each has an "EAC Filter" column for a Y/N of whether to include the cost in an Estimate at Completion, which automatically changes over time and/or as data is added. Here are examples:

enter image description here enter image description here enter image description here

EAC from the three tables can be calculated as follows:

Total Cost = Sum(Forecast[Cost])+Sum(Actual[Cost])+Sum(Invoice[Cost])
EAC = Calculate([Total Cost],EAC_Filter[EAC Filter]="Y")

I have a budget at the "Account" level, which could also be rolled up to a "Dept" level budget.

enter image description here enter image description here enter image description here

I need a measure for my Power Pivot table which will display the week at which costs have exceeded, or are forecasted to exceed 75% of the budget, using some sort of a cumulative cost, combined with the max week where cumulative cost >= .75 * Budget.

The weeks are numbered through the year as follows:

enter image description here

Thanks for your help!

Solution

  • Given an EAC measure which sums the cost per week,

    EAC = CALCULATE(SUM(Forcast[Cost]) + SUM(Acutal[Cost]) + SUM(Invoice[Cost]),
                EAC_Filter[EAC Filter] = "Y")

    You can create a Cumulative Cost measure as follows:

    Cumulative Cost = CALCULATE([EAC],
                      FILTER(ALL('Calendar'), 'Calendar'[Week] <= MAX('Calendar'[Week])))

    Using this, we can create a measure that predicts the week the cost exceeds 75% of the budget:

    75% Week = MINX(FILTER(ALL('Calendar'), [Cumulative Cost] > 0.75 * SUM(Budget[Budget])),
               'Calendar'[Week])

    Here's what the relationships structure looks like:

    Relationships