TOTALYTD in Distinct count

[rastoiyashu4]

Hi Team,

I am new to Bi and a requirement came in front of me to show the data in a cummulative way. I was using the TotalYTD for the same but the expression which i am using in YTD contains the DISTINCT COUNT not the sum. I don't know whether the YTD works for DISTINCTCOUNT or not. For better understanding below is the measure which i am using in YTD. 
DAX QUERY = CALCULATE(DISTINCTCOUNT('SA Program'[Self Assessment Program Tracker Number]).

The problem statement something like this. Suppose in a year 2021 i have 40 programs, 2022 i have 10 and in 2023 i have 10 so it should show 60 when i select 2023 in year slicer or if i select 2022 in slicer it should give me 50. It's not showing as of now. Please help me it's urgent. 

[rastoiyashu4]

Hi !!
Thanks for your answer the same thing i applied and got the answers. But thanks for your time and efforts. Really Appreciate. 

[danextian · Accepted solution]

Hi @rastoiyashu4 ,

What  you're trying to achieive is not YTD but rather a cumulative count or sum. The concept of YTD is tha a calculation resets at every change of year (calendar or fiscal).  Try this instead:

cumulative =
CALCULATE (
    DISTINCTCOUNT ( 'table'[column] ),
    FILTER ( ALL ( 'table' ), 'table'[year] = MAX ( 'table'[year] ) )
)

the formula above assumes that you don't have  separate dates table and you're only calculating for the cumulative sum/count by  year and not for every date.