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
Fabcon_Europe_Social_Bogo

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Find out what's new and trending in the Fabric Community.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.