Forum Discussion

vckbx's avatar
vckbx
Helper I
5 years ago
Solved

Distinct Count on Last Date

Hi - I have data in this format:

ItemPartyDate
1A1/1/2021
2A1/1/2021
3A1/1/2021
4A1/1/2021
1A2/1/2021
2A2/1/2021
3A2/1/2021
4A2/1/2021
2A3/1/2021
3A3/1/2021
4A3/1/2021

 

I want to be able to slide a date slicer and get a distinct count of item by party on the last date in the date range.

 

I've tried several variations of this:

LastItems = CALCULATE(DISTINCTCOUNT(Table[Item]),FILTER(ALLSELECTED(Table),Table[Date]=LASTDATE(Table[Date])))
 
But it is returning the distinct count for the entire date range I've selected, instead of just on the final date.
 
Let me know if more information is needed!
  • PaulOlding's avatar
    PaulOlding
    5 years ago
    Does this do the job?
     
    LastItems =
    VAR _LastDate = MAX('Table'[Date])
    RETURN
    CALCULATE(
    DISTINCTCOUNT('Table'[Item]),
    'Table'[Date]=_LastDate
    )

2 Replies

  • PaulOlding's avatar
    PaulOlding
    Solution Sage
    5 years ago
    Does this do the job?
     
    LastItems =
    VAR _LastDate = MAX('Table'[Date])
    RETURN
    CALCULATE(
    DISTINCTCOUNT('Table'[Item]),
    'Table'[Date]=_LastDate
    )
    • vckbx's avatar
      vckbx
      Helper I
      5 years ago

      That did it! Thanks. Any explanation as to why that works vs my method?