Most Recent Date and Second Most Recent Date

[rweb95]

Hello Power BI community. I'm trying to find the most recent date entry in a table, the second most recent date, and then take the difference between the two to use as a logical test in an IF statement. Here is a small example of the data:

Title              LatestEffectiveDate

San Diego      4/1/2019

San Diego      4/1/2020

San Diego      4/1/2018

New York     10/1/2020

New York       5/2/2019

New York       9/1/2020

I want to find the most recent and second most recent date for each location, then take the DATEDIFF between them in # of months. So San Diego should be 12 months and New York should be 1 month. Then I'm trying to use this date difference in an IF statement as follows:

IF(

'SP-Update Log'[DiffBetweenLastTwoEffDates] >= 6

&& 'SP-Update Log'[DiffBetweenLastTwoEffDates] <= 11,

SWITCH(

TRUE(),

RELATED(Utility[Usage]) > 1, "Semi-Annual",

RELATED(Utility[Usage]) = 1, "Annual",

RELATED(Utility[Usage]) = 0, "Unused"),

IF(

'SP-Update Log'[DiffBetweenLastTwoEffDates] >= 12,

SWITCH(

TRUE(),

RELATED(Utility[Usage]) >= 1, "Annual",

RELATED(Utility[Usage]) = 0, "Unused")))

Here is what I have tried for the most recent effective date, second most recent, and the date difference:

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

2. SecondMostRecentDate = CALCULATE (

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

FILTER (

'SP-Update Log',

'SP-Update Log'[LatestEffectiveDate] < EARLIER ( 'SP-Update Log'[LatestEffectiveDate])))

3. DifferenceBetweenTwoDates = DATEDIFF('SP-Update Log'[SecondMostRecentDate], 'SP-Update Log'[MostRecentEffectiveDate], MONTH)

The 1st formula seems to work, the 2nd one is not, and so I'm getting incorrect results from the 3rd.

I appreciate anyone's help with this and will provide more info if needed!

[AlB · Accepted solution]

Hi @rweb95 

Are all those 3 (1., 2., 3.) calculated columns that you are trying to add to the table above? Assuming it is so:

MostRecentEffectiveDate =
CALCULATE (
    MAX ( 'SP-Update Log'[LatestEffectiveDate] ),
    ALLEXCEPT ( 'SP-Update Log', 'SP-Update Log'[Title] )
)

SecondMostRecentDate =
CALCULATE (
    MAX ( 'SP-Update Log'[LatestEffectiveDate] ),
    ALLEXCEPT ( 'SP-Update Log', 'SP-Update Log'[Title] ),
    'SP-Update Log'[LatestEffectiveDate]
        < EARLIER ( 'SP-Update Log'[MostRecentEffectiveDate] )
)

DifferenceBetweenTwoDates = DATEDIFF('SP-Update Log'[SecondMostRecentDate],'SP-Update Log'[MostRecentEffectiveDate], MONTH)

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[rweb95]

Yes! It all appears to work. I never thought about using ALLEXCEPT.

Thank you!