Search code examples
exceldaxpowerpivot

DAX Running Total and 75% Complete Date

I would like to forecast the month (as a date) at which 75% of the account ceilings will be spent. Here are three very generic example tables for a much more complicated dataset/scenario, but I think a simple solution here will get me on my way. I will actually be using weeks, but months is simpler for this example.

enter image description here enter image description here

enter image description here

So, I need a cumulative, or running total of Cost which I believe is: 

Cumulative Cost:=Calculate(Sum(Cost[Cost],filter(all(Months[Month],Months[Month]<=max(Months[Month])))

But then I'm stuck on finding the Month at which: [Cumulative Cost]>=.75*[Ceiling]

I will display the date for each Account in my Power Pivot Table. Any ideas?

I'm using Power Pivot in Excel 2016

Thanks in advance

Solution

  • You can create a calculated column for cumulative cost on your Cost table as follows:

    Cumulative Cost = CALCULATE(SUM(Cost[Cost]),
                            FILTER(ALLEXCEPT(Cost, Cost[Account]),
                                   Cost[Month] <= EARLIER(Cost[Month])))

    Once you have that, your 75% complete date can be calculated using a minimum function:

    75% Complete Date = MINX(FILTER(Cost, Cost[Cumulative Cost] >= 0.75*Accounts[Ceiling]),
                         Cost[Month])

    This finds the minimal date where the cumulative cost is greater than 75% of the ceiling.