Forum Discussion

diogoricciardi's avatar
diogoricciardi
Frequent Visitor
5 years ago

Distinct Count YTD by Month

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

 

3 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    5 years ago

    diogoricciardi , Try a measure like

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

    • diogoricciardi's avatar
      diogoricciardi
      Frequent Visitor
      5 years ago

      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's avatar
        amitchandak
        Icon for Super User rankSuper User
        5 years ago

        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