Forum Discussion
DISTINCTCOUNT FOR MEASURES (YTD CALCULATION)
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.
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.
4 Replies
- Adescrit
Impactful Individual
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.
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
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.
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.