Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
49 | |
26 | |
20 | |
15 | |
12 |
User | Count |
---|---|
57 | |
49 | |
44 | |
19 | |
18 |