cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
rweb95
Frequent Visitor

Most Recent Date and Second Most Recent Date

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!
 
1 ACCEPTED SOLUTION
AlB
Super User III
Super User III

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 

 

SU18_powerbi_badge

View solution in original post

2 REPLIES 2
AlB
Super User III
Super User III

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 

 

SU18_powerbi_badge

View solution in original post

rweb95
Frequent Visitor

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

 

Thank you!

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Top Solution Authors