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 All,
Does anyone know how I can create a flag to mark on the second Max month?
Below is my data set
Date | Flag |
1/01/2021 | 0 |
1/02/2021 | 0 |
1/03/2021 | 0 |
1/04/2021 | 0 |
1/05/2021 | 1 |
1/06/2021 | 0 |
As you can see that 1/06/21 is my max month in this table, but I want to put a flag on the month before 1/05/21.
When 01/07/21 data is available, then the flag "1" will be moved to 01/06/21.
Date | Flag |
1/01/2021 | 0 |
1/02/2021 | 0 |
1/03/2021 | 0 |
1/04/2021 | 0 |
1/05/2021 | 0 |
1/06/2021 | 1 |
1/07/2021 | 0 |
Could someone please help me?
Thanks.
Cheers,
Kev
Solved! Go to Solution.
Hi Kev,
Use this in calculated column.
Flag =
IF (
'Table'[Date]
= CALCULATE (
MAX ( 'Table'[Date] ),
FILTER ( 'Table', 'Table'[Date] <> MAX ( 'Table'[Date] ) )
),
1,
0
)
Thanks
Hi @BigKev
You can try my way to build a rank by var function and use if to show result.
Flag =
VAR _RANK = RANKX('Table','Table'[Date],,DESC,Dense)
Return
IF(_RANK = 2,1,0)
Result:
Update the data:
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Seems other solutions aren't robust enough when a month contains more than one date.
Flag =
VAR __max = MAX( 'Table'[Date] )
RETURN
IF( ISEMPTY(
FILTER(
CALCULATETABLE(
PREVIOUSMONTH( 'Table'[Date] ),
'Table'[Date] = __max,
REMOVEFILTERS()
),
'Table'[Date] = EARLIER( 'Table'[Date] )
)
),
0,1
)
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
Hi Kev,
Use the below formula.
Thanks
Hari
Please mark answer if it resolves your problem
Hi, @BigKev
Please check the below picture and the sample pbix file's link down below.
It is for creating a measure, not a column.
Hi, My name is Jihwan Kim.
If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.
Linkedin: linkedin.com/in/jihwankim1975/
Twitter: twitter.com/Jihwan_JHKIM
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
Hey Jihwan,
Do you know how I can achieve this on the calculated column instead of measures?
Cheers,
Kev
Hi, @BigKev
Thank you for your feedback.
Please check the link down below.
Second Max Flag CC =
VAR newtable =
TOPN ( 2, 'Table', 'Table'[Date], DESC )
RETURN
IF ( 'Table'[Date] = MINX ( newtable, 'Table'[Date] ), 1, 0 )
https://www.dropbox.com/s/5ga7njcyc4w47rh/bigkevv2.pbix?dl=0
Hi, My name is Jihwan Kim.
If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.
Linkedin: linkedin.com/in/jihwankim1975/
Twitter: twitter.com/Jihwan_JHKIM
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
Hi Kev,
Use this in calculated column.
Flag =
IF (
'Table'[Date]
= CALCULATE (
MAX ( 'Table'[Date] ),
FILTER ( 'Table', 'Table'[Date] <> MAX ( 'Table'[Date] ) )
),
1,
0
)
Thanks
Hey @Hariharan_R,
Thanks for the answer. Just wondering, instead of just flag the second max month, how can I flag the second max month and before?
This is what I want to achieve:
Date Flag
1/01/2021 1
1/02/2021 1
1/03/2021 1
1/04/2021 1
1/05/2021 1
1/06/2021 0
Hi @BigKev
Try this measure:
Flag =
VAR _Max = MAX ( 'Table'[Date] ) RETURN IF ( 'Table'[Date] < _Max, 1, 0 )
Result is as below.
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
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 |
---|---|
97 | |
96 | |
81 | |
74 | |
66 |
User | Count |
---|---|
126 | |
106 | |
105 | |
86 | |
72 |