Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
Check out the May 2024 Power BI update to learn about new features.
User | Count |
---|---|
71 | |
38 | |
21 | |
20 | |
13 |
User | Count |
---|---|
125 | |
41 | |
38 | |
26 | |
24 |