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.
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..
Community Supportv-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.