Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
vckbx
Helper I
Helper I

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!
1 ACCEPTED SOLUTION
PaulOlding
Solution Sage
Solution Sage

Does this do the job?
 
LastItems =
VAR _LastDate = MAX('Table'[Date])
RETURN
CALCULATE(
DISTINCTCOUNT('Table'[Item]),
'Table'[Date]=_LastDate
)

View solution in original post

2 REPLIES 2
PaulOlding
Solution Sage
Solution Sage

Does this do the job?
 
LastItems =
VAR _LastDate = MAX('Table'[Date])
RETURN
CALCULATE(
DISTINCTCOUNT('Table'[Item]),
'Table'[Date]=_LastDate
)

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

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.