Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hello All,
I would like to create a column with flag 1 or 0 based on two date columns. Here is the sample data:
KPI Name | Month | Modified |
KPI1 | 1/3/2019 | 2/6/2019 |
KPI1 | 2/5/2019 | 2/5/2019 |
kpi1 | 2/5/2019 | 2/6/2019 |
I want to take the max of month first. So in this case, it will be 2/5/2019 with two modified dates, then I have to take the max of modified date. So final output should be something like this:
KPI Name | Month | Modified | Is Latest |
KPI1 | 1/3/2019 | 2/6/2019 | 0 |
KPI1 | 2/5/2019 | 2/5/2019 | 0 |
kpi1 | 2/5/2019 | 2/6/2019 | 1 |
Only the last row should be returned.
I tried a couple of dax queries and it doesnt yield the result I want. Can someone help ?
Solved! Go to Solution.
Try this as calculated column
IsLatest = IF ( AND ( [Month] = MAX ( [Month] ), [Modified] = CALCULATE ( MAX ( [Modified] ), FILTER ( Table1, [Month] = MAX ( [Month] ) ) ) ), 1, 0 )
Try this as calculated column
IsLatest = IF ( AND ( [Month] = MAX ( [Month] ), [Modified] = CALCULATE ( MAX ( [Modified] ), FILTER ( Table1, [Month] = MAX ( [Month] ) ) ) ), 1, 0 )