Forum Discussion

qmestu's avatar
qmestu
Helper IV
7 months ago
Solved

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...
  • DanieleUgoCopp's avatar
    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 rows

    you 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.