Forum Discussion
qmestu
7 months agoHelper IV
How to count indicators per classification based on the latest date (DAX)
Hi, Using the sample data below, how can I calculate the count of indicators per classification, considering only the latest available date for each indicator? The DAX measure I’m using currently...
- 7 months ago
Hello,
I think the issue is that LASTDATE(Table[Date]) returns only the last date in the current filter context, so indicators that have multiple months in the same quarter are still counted multiple times
what you need instead is to first identify, for each Indicator-Key, its own latest available date, and then count indicators only on those rowsyou can do it like this:
Indicators Latest := CALCULATE( DISTINCTCOUNT(Table[Indicator-Key]), FILTER( Table, Table[Date] = CALCULATE( MAX(Table[Date]), ALLEXCEPT(Table, Table[Indicator-Key]) ) ) )This should works because, for every indicator, it keeps only the row where the date is the maximum for that indicator, and all earlier months are ignored,
when you put Classification on rows, the indicator is counted only once, under its latest classification.
Ashish_Mathur
7 months agoSuper User
Hi,
Based on the table that you have shown, please show the expected result.