topic Re: Distinct Count YTD by Month in DAX Commands and Tips

Distinct Count YTD by Month

diogoricciardi — Thu, 11 Feb 2021 12:29:06 GMT

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

Re: Distinct Count YTD by Month

amitchandak — Thu, 11 Feb 2021 13:00:32 GMT

diogoricciardi , Try a measure like

YTD Sales = CALCULATE(SUMX(values('Date'[Month Year]), calculate(Distinctcount(Table[ID]))),DATESYTD('Date'[Date],"12/31"))

Re: Distinct Count YTD by Month

diogoricciardi — Thu, 11 Feb 2021 14:06:46 GMT

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..

Re: Distinct Count YTD by Month

amitchandak — Thu, 11 Feb 2021 14:55:02 GMT

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