Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
Hi everyone,
Hope you're all safe.
I'm having some trouble trying to figure out how to calculate a YTD distinct count by month.
To be more specific, when I use TOTALYTD function with DISTINCTCOUNT it counts the distinct values using the entire table, which means, if there's a value that appears both on January and on February, it will count as 1.
What I would like to do is to count these values separately, the distintict count of january + the distinct count of february and so on.
Is there an option for doing it automatically? I mean, without having to count each month separately and adding afterwards?
Any help will be very much appreciated.
Thanks
@diogoricciardi , Try a measure like
YTD Sales = CALCULATE(SUMX(values('Date'[Month Year]), calculate(Distinctcount(Table[ID]))),DATESYTD('Date'[Date],"12/31"))
Holy crap, that worked...
Sorry to be a pain, but can you explain to me what is really going on in this function? I could't figure it out.
Thank man..
@diogoricciardi , values('Date'[Month Year]) is forcing the data to be distinct count till month level, and post that sumx will work. A level of details implementation
User | Count |
---|---|
21 | |
20 | |
15 | |
10 | |
7 |
User | Count |
---|---|
28 | |
28 | |
12 | |
12 | |
12 |