Date Difference Between Two Most Recent Dates

[rweb95]

Hello Power BI community!

I need some help trying to find the difference in months between the most recent date entry and the second most recent date entry.

I have two measures to find these dates:

1. CalcLatestEffDate = MAX( 'SP-Update Log'[LatestEffectiveDate])

2. SecondMostRecentEffDate =

VAR currentdate = [CalcLatestEffDate]

RETURN

CALCULATE(MAX( ('SP-Update Log'[LatestEffectiveDate])), 'SP-Update Log'[LatestEffectiveDate] < currentdate)

Both of these measures seem to return the correct dates.

Next I created a calculated column to find the DateDiff:

DiffBetweenLastTwoEffDates =

DATEDIFF([SecondMostRecentEffDate], [CalcLatestEffDate], MONTH)

This returns nothing but blanks.

Any thoughts on how to fix this issue?

[dedelman_clng · Accepted solution]

Hi @rweb95  - 

By using measures to create a column, the context for the column calculation is just the row itself, which is only going to have one date on it.

If you want to display the date difference as a "column" in a table or matrix, use the same formula but change the column to a measure and add it to your visualization. This will calculate the value in the correct context.

Hope this helps

David