Forum Discussion
Helper IPlotting a cumulative chart based on distinct counts
matthewtjy , Have year column in your table or use one from the Date hierarchy and try measure like
New column
Year = year([SUBMIT_DT])
New measure
Cumm Sales = CALCULATE(distinctcount('Table'[WORKPLACE_NO]),filter(allselected('Table'),'Table'[SUBMIT_DT] <=max('Table'[SUBMIT_DT])))
New measure with Date table, having year column
Cumm Sales = CALCULATE(distinctcount('Table'[WORKPLACE_NO]),filter(allselected('Date'),'Date'[Date] <=max('Date'[Date])))
To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.
Helper ISorry what do you mean by do I have a date? I would like to plot a chart based on the cumulative distinct count as at the end of each year e.g. end 2014, end 2015, end 2016, ..., all in 1 chart.
Sorry I'm not sure how else to explain it more simply, I'd post a screenshot of my excel file as it is easier to explain but I'm not sure how to upload a screenshot.
Thanks for replying though!
Super Usermatthewtjy , Have year column in your table or use one from the Date hierarchy and try measure like
New column
Year = year([SUBMIT_DT])
New measure
Cumm Sales = CALCULATE(distinctcount('Table'[WORKPLACE_NO]),filter(allselected('Table'),'Table'[SUBMIT_DT] <=max('Table'[SUBMIT_DT])))
New measure with Date table, having year column
Cumm Sales = CALCULATE(distinctcount('Table'[WORKPLACE_NO]),filter(allselected('Date'),'Date'[Date] <=max('Date'[Date])))
To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.
- matthewtjy
thanks!