Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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:
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 (
Solved! Go to 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
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
Yes! It all appears to work. I never thought about using ALLEXCEPT.
Thank you!
User | Count |
---|---|
48 | |
44 | |
19 | |
14 | |
13 |
User | Count |
---|---|
102 | |
55 | |
28 | |
19 | |
14 |