Powerpivot - Determine how much a user-input date changes over time

Question

I have a table with a series of monthly measurements for several cases. One of the measures per case is the date on which an analyst predicts that case will close (the Forecast Date).

In Month 1, an analyst might think a case is going to close on June 30, 2015. But then in Month 2, that analyst might revise the estimate and say that case will close on August 15.

What I want to do is create a calculated column that tells me the difference between last month's Forecast Date and this month's Forecast Date.

Assuming Month 1 is Jan 2015, what I want is the diff column:

CaseNum | MeasureMonth | ForecastDate | DiffFromPrevMeasure 
case1   | 2015/01/31   | 2015/06/30   |-- (there is no previous date)
case1   | 2015/02/28   | 2015/08/15   | 46
case1   | 2015/03/31   | 2015/08/01   | -14

And so on. There are obviously many cases in the table.

I need to do things like examine the distribution of changes, show what % of changes are +/- before and after a certain date, and stuff like that. So I can't do it via a measure.

I am thinking I need to do something with CALCULATE / MAX / EARLIER minus current row's date, but I just can't make it work.

I'm using Excel 2010, not 2013, if that's important to the solution.

Answer 1

The same thing but different...

Create a new calculated column (PreviousMeasureMonth) to store the previous month's measure date:

=
CALCULATE (
    MAX ( myTable[MeasureMonth] ),
    FILTER (
        ALL ( myTable ),
        myTable[MeasureMonth] < EARLIER ( myTable[MeasureMonth] )
            && myTable[casenum] = EARLIER ( myTable[CaseNum] )
    )

Add another calculated column (PreviousForecastDate) to pull out the ForecastDate for that calculated previous measure month:

=
CALCULATE (
    MAX ( myTable[ForecastDate] ),
    FILTER (
        ALL ( myTable ),
        myTable[MeasureMonth] = EARLIER ( [PreviousMeasureMonth] )
            && myTable[CaseNum] = EARLIER ( myTable[CaseNum] )
    )
)

Then finally add a calculated column to subtract the current forecast date from last month's forecast date:

=[ForecastDate]-[PreviousForecastDate]

Answer 2

This is one of those that would be very simple in SQL but is pretty tricky in DAX.

=
 CALCULATE (
   AVERAGE ( [ForecastDate] ),
   TOPN (
    1,
    FILTER (
            myTable,
            [CaseNum] = EARLIER ( [CaseNum] )
            && [MeasureMonth] < EARLIER ( [MeasureMonth] )
            ),
    [MeasureMonth]
)) - [ForecastDate]

TOPN takes the nth value of a table you provide, the trick here is that the table can be filtered. I specify a value of for the top row then use FILTER() to pass a table that is all that caseNum's records with a date earlier than the one in that record (its the < that does this not the EARLIER()). I then use [MeasureMonth] as the argument to sort the table and deduct the forecastdate to get the change.

With this measure the first record for each will give you a crazy number, one way to deal with this is to introduce an IF() that checks whether the CALCULATE is BLANK().

Hope this makes sense.