Counting the values at the end of each quarter

[Kolumam]

Hi All,

I am trying to count the number of values at the end of each quarter. If you see my below result for number of Cleanings, it should be 9 for Qtrr 1 2019 and 5 for Qtr 4 2018 but it counts the total and puts 14 in Qtr1 2019. How do I fix this?

IS my formula correct?

Number of Cleanings = CALCULATE(DISTINCTCOUNT('Cleaning Activity'[Id]),FILTER('Cleaning Activity','Cleaning Activity'[Date of Cleaning]<=ENDOFQUARTER('Month'[Month])))

Quarter is a calculated column from Months table.

The expected result is the below:

[tex628 · Accepted solution]

It looks like your 'Cleaning Activity' table has some issues with the relationship to the 'Month' table. Try applying a 'Cleaning Activity'[Date of Cleaning]>=STARTOFQUARTER(Month[Month]) filter to the measure aswell. 

Number of Cleanings = 
CALCULATE(DISTINCTCOUNT('Cleaning Activity'[Id]),
FILTER(
'Cleaning Activity',
'Cleaning Activity'[Date of Cleaning]<=ENDOFQUARTER('Month'[Month])
&&
'Cleaning Activity'[Date of Cleaning]>=STARTOFQUARTER('Month'[Month])
))