Distinct Count YTD by Month

[diogoricciardi]

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

[amitchandak]

@diogoricciardi , Try a measure like

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

[diogoricciardi]

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

[amitchandak]

@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