Forum Discussion

rweb95's avatar
rweb95
Frequent Visitor
5 years ago
Solved

Date Difference Between Two Most Recent Dates

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's avatar
    dedelman_clng
    5 years ago

    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

1 Reply

  • dedelman_clng's avatar
    dedelman_clng
    Community Champion
    5 years ago

    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