DISTINCTCOUNT FOR MEASURES (YTD CALCULATION)

[Anonymous]

Hello All,

I have a certain number of customers and currently i have MTD calculation, in this case usually DISTINTCOUNT works pretty well: i add the function + column, and it's done.

But as second calculation i need "YTD customers". I need to apply DISTINCTCOUNT to an existing measure, not to a column, but it doesn't work.

Is there any other function which same proposal (ignore repetitians) which could be applied to measures?

Thanks for your help.

[Anonymous]

Hello @Adescrit.,

I already applied DISTINTCOUNT as prior calculation to my measures, but as i mentioned at the beginnining, now i have a YTD calculation, and for this one i need to remove the repetitions since there're the same customers in different months.

"Total OB_EUR Customers" is a distinctcount measure, and now i have to apply YTD + DISTINCTCOUNT as you see below, but how? there's no way to divide it by parts.

[Adescrit]

Hi @Anonymous ,

What is the current formula you're using to try to calculate the YTD customers?

Could you repeat the calculation you're using to calculate the number of customers, but then add a filter condition for YTD?

Something like this:

YTD Customers = 
CALCULATE( DISTINCOUNT( 'Customers'[ID] ), DATESYTD( Date[ Date ] ) )

This requires you to have a date dimension table (Date[Date]) and the Customers[Id] is your  list of customers so replace as necessary.

[Anonymous]

Hello @Adescrit,

i'm using exactly this formula, but as you wrote "Customers [ID]" is a column, and i have a measure, which doesn't work for this kind of function.

  It doesn't work for measures:

Thanks.

[Adescrit · Accepted solution]

Hi @Anonymous ,

You can't apply DISTINCTCOUNT over a measure.

I'm using the below formulas and it works:

Total OB EUR Customers = DISTINCTCOUNT( Sales[Customer Id] )

Total OB EUR YTD Customers = CALCULATE( [Total OB EUR Customers], DATESYTD( dCalendar[Date] ) )

Because the # customers measure is already calculating the distinct count of cutomers, you can just calculate that measure without having to re-calculate a distinct count.