topic Distinct Count on Last Date in DAX Commands and Tips

Distinct Count on Last Date

vckbx — Mon, 28 Jun 2021 14:44:08 GMT

Hi - I have data in this format:

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 — Mon, 28 Jun 2021 16:04:15 GMT

Does this do the job?

LastItems =

VAR _LastDate = MAX('Table'[Date])

RETURN

CALCULATE(

DISTINCTCOUNT('Table'[Item]),

'Table'[Date]=_LastDate

)

vckbx — Mon, 28 Jun 2021 17:30:33 GMT

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