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.
Hi,
I have quarterly date and want to get the latest 2 date, however there is condition in which Dec data is considered as the official one and need to be included.
For example:
31-Dec-21 |
31-Mar-22 |
30-Jun-22 |
31-Oct-22 |
the desired result is: 31-Oct-2022 and 31-Dec-2021
while for the following data:
31-Dec-21 |
31-Mar-22 |
30-Jun-22 |
31-Oct-22 |
31-Dec-22 |
31-Mar-23 |
30-Jun-23 |
31-Oct-23 |
31-Dec-23 |
the desired result is: 31-Dec-2023 and 31-Dec-2022.
Tried the following but no luck:
any comments are highly appreciated.
thanks
First, let's always get the latest December date. Then, we'll get the latest date from the entire dataset. If this latest date is the same as the December date, we'll then get the second latest date. If not, then the two dates we want are the latest date and the December date.
Here's how you can modify your DAX:
Latest 2 Dates =
VAR LastDateInDecember = CALCULATE(MAX('Table1'[Date]), MONTH('Table1'[Date]) = 12)
VAR MaxDate = MAX('Table1'[Date])
VAR SecondLatestDate =
IF(
MaxDate = LastDateInDecember,
CALCULATE(MAX('Table1'[Date]), 'Table1'[Date] < MaxDate),
CALCULATE(MAX('Table1'[Date]), 'Table1'[Date] < LastDateInDecember)
)
RETURN
{LastDateInDecember, SecondLatestDate}
So, what we're doing here is first getting the latest December date. Then, we're getting the absolute latest date. If this latest date is the same as the December date, we get the second latest date. Otherwise, our two dates are the latest December date and the absolute latest date. This should give you the desired results for the examples you provided.
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 |
---|---|
56 | |
27 | |
24 | |
14 | |
9 |
User | Count |
---|---|
76 | |
58 | |
47 | |
17 | |
12 |