Forum Discussion
TOTALYTD in Distinct count
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.
Hi Anonymous ,
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.
2 Replies
Hi Anonymous ,
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.