Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
Solved! Go to 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.
Proud to be a Super User!
Hi !!
Thanks for your answer the same thing i applied and got the answers. But thanks for your time and efforts. Really Appreciate.
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.
Proud to be a Super User!
User | Count |
---|---|
102 | |
92 | |
85 | |
78 | |
71 |
User | Count |
---|---|
113 | |
104 | |
101 | |
73 | |
65 |