The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now
I have a table that has multiple records for each day of the year for multiple entities. I need to create a column that will flag the newest record for each day as per the below example. Can someone please help me to achieve this with DAX?
branchID | date | amount | recorded | latest |
1234 | 15/10/2019 | 220 | 15/10/2019 | |
1234 | 15/10/2019 | 225 | 16/10/2019 | |
1234 | 15/10/2019 | 245 | 17/10/2019 | yes |
5678 | 15/10/2019 | 115 | 15/10/2019 | |
5678 | 15/10/2019 | 130 | 16/10/2019 | |
5678 | 15/10/2019 | 142 | 17/10/2019 | yes |
Solved! Go to Solution.
Hi @4-3-3 ,
I modified my formula that you can have a try.
Flag = var max_recorded = CALCULATE(MAX('Table'[recorded]),ALLEXCEPT('Table','Table'[date])) return IF('Table'[recorded] = max_recorded,"Yes",BLANK())
Best Regards,
Xue Ding
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Please check, if this example can help
https://community.powerbi.com/t5/Desktop/Most-Recent-Record/td-p/675012
Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks.
Thanks for your input. Unfortunatetly that example only grouping by one column and I need this to look at two..
Hi @4-3-3 ,
I created a measure that may help you.
Measure = var max_recorded = CALCULATE(MAX('Table'[recorded]),ALLEXCEPT('Table','Table'[branchID])) return IF(MAX('Table'[recorded]) = max_recorded,"Yes",BLANK())
Best Regards,
Xue Ding
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi there, thanks for trying to help, it is much appreciated. Unfortunately this is not doing what I am after.
It measure needs to be looking on two columns - hence the question about using two columns in a group-by formula as that is how I interpret it in my head but it may not be the correct term..
Anyway, I have prepared a better file that will hopefully be of a help to explain what I am after - i have added the 'latestFlag' column to mark the lines exactly how I need them to be.
Hi @4-3-3 ,
I modified my formula that you can have a try.
Flag = var max_recorded = CALCULATE(MAX('Table'[recorded]),ALLEXCEPT('Table','Table'[date])) return IF('Table'[recorded] = max_recorded,"Yes",BLANK())
Best Regards,
Xue Ding
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Beautiful! I did not realize that I can actually leave out the branchID from the measure all together as I can just use filter later on.
Thanks so much for helping!
Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.
User | Count |
---|---|
157 | |
106 | |
96 | |
83 | |
75 |
User | Count |
---|---|
153 | |
137 | |
131 | |
81 | |
61 |