Forum Discussion
Measure not returning the LATEST value in category
Hi awg201,
Please refer the below atached .PBIX file.
If this post helps, then please give us ‘Kudos’ and consider Accept it as a solution to help the other members find it more quickly.
Thank you.
Community SupportHi awg201,
Thank you for reaching you ti the Microsoft Forum Community.
I have created a measure that uses ranking based on CaseEndDate. You can adjust the ranking logic to suit your specific requirements and apply the necessary filtering as shown below
Attached the Pbix file for your reference.
If you need help please share the additional data to provide exact solution.
Hi v-saisrao-msft , thanks for your help
For some reason, when I apply this to a larger dataset, it just returns a single row with an unexpected result.
So I tried it in your example by adding more data, and got the same outcome:
Here is the expanded DATASET:
| CaseID | CaseStartDate | CaseEndDate | LocationName |
| A | 17-Dec-24 | 19-Dec-24 | SYDNEY |
| A | 11-Sep-24 | 11-Sep-24 | HOBART |
| A | 11-Feb-25 | 13-Feb-25 | MELBOURNE |
| A | 26-Apr-24 | 26-Apr-24 | SYDNEY |
| B | 01-Jan-24 | 06-Jan-24 | BRISBANE |
| B | 03-Jun-23 | 08-Jun-23 | SINGAPORE |
| C | 12-Feb-24 | 16-Feb-24 | TEXAS |
| C | 15-Mar-24 | 18-Mar-24 | NEW YORK |
| D | 22-Jul-24 | 25-Jul-24 | LONDON |
| D | 05-Nov-24 | 07-Nov-24 | PARIS |
| E | 10-Aug-24 | 12-Aug-24 | TOKYO |
| E | 19-Sep-24 | 21-Sep-24 | BERLIN |
| F | 02-Oct-24 | 05-Oct-24 | SYDNEY |
| F | 14-Dec-24 | 16-Dec-24 | MELBOURNE |
| G | 23-Jan-25 | 25-Jan-25 | BRISBANE |
| G | 08-Apr-25 | 10-Apr-25 | SINGAPORE |
| G | 23-Jun-25 | 25-Jun-25 | NEW ZEALAND |
| H | 17-May-25 | 19-May-25 | TEXAS |
Below would be the desired result:
Sliced for 2024
| CaseID | CaseStartDate | CaseEndDate | LocationName |
| A | 17-Dec-24 | 19-Dec-24 | SYDNEY |
| B | 01-Jan-24 | 06-Jan-24 | BRISBANE |
| C | 15-Mar-24 | 18-Mar-24 | NEW YORK |
| D | 05-Nov-24 | 07-Nov-24 | PARIS |
| E | 19-Sep-24 | 21-Sep-24 | BERLIN |
| F | 14-Dec-24 | 16-Dec-24 | MELBOURNE |
for 2025:
| CaseID | CaseStartDate | CaseEndDate | LocationName |
| A | 11-Feb-25 | 13-Feb-25 | MELBOURNE |
| G | 23-Jun-25 | 25-Jun-25 | NEW ZEALAND |
| H | 17-May-25 | 19-May-25 | TEXAS |
it would proably need to be dynamic as we may need to drill down into months as well... thanks again for your help. I've spent way too long trying to ge this to work..
- v-saisrao-msft
v-saisrao-msft thanks again. I think this would have to be a dynamic measure rather than a column. Essentialy, a case should only be counted once in a period, whether it be a month or a year.
I think what you had initally was close, but for some reason, it did not like it when a larger dataset was used.
CaseID CaseStartDate CaseEndDate LocationName A 17-Dec-24 19-Dec-24 SYDNEY A 11-Sep-24 11-Sep-24 HOBART A 11-Feb-25 13-Feb-25 MELBOURNE A 26-Apr-24 26-Apr-24 SYDNEY B 01-Jan-24 06-Jan-24 BRISBANE B 03-Jun-23 08-Jun-23 SINGAPORE C 12-Feb-24 16-Feb-24 TEXAS C 15-Mar-24 18-Mar-24 NEW YORK D 22-Jul-24 25-Jul-24 LONDON D 05-Nov-24 07-Nov-24 PARIS E 10-Aug-24 12-Aug-24 TOKYO E 19-Sep-24 21-Sep-24 BERLIN F 02-Oct-24 05-Oct-24 SYDNEY F 14-Dec-24 16-Dec-24 MELBOURNE G 23-Jan-25 25-Jan-25 BRISBANE G 08-Apr-25 10-Apr-25 SINGAPORE G 23-Jun-25 25-Jun-25 NEW ZEALAND H 17-May-25 19-May-25 TEXAS If we just focus on Case A, when I slice for 2024 for example, I should see:
CaseID CaseStartDate CaseEndDate LocationName A 17-Dec-24 19-Dec-24 SYDNEY but when i further filter or drill for the month Febraury in 2024, i should only get
CaseID CaseStartDate CaseEndDate LocationName A 11-Feb-25 13-Feb-25 MELBOURNE - v-saisrao-msft
Hi awg201,
Please refer the below atached .PBIX file.
If this post helps, then please give us ‘Kudos’ and consider Accept it as a solution to help the other members find it more quickly.
Thank you.
- v-saisrao-msft
Hi awg201,
I hope this information is helpful. Please let me know if you have any further questions or if you'd like to discuss this further. If this answers your question, please Accept it as a solution and give it a 'Kudos' so others can find it easily.
Thank you. - v-saisrao-msft
Hi awg201,
May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.
Thank you.