Forum Discussion
running total in combination with DistinctCount
Dear,
I have a table which is linked to a TimeTable. The table holds column 'Year' and 'name_active'. What I want to achieve is to list the number of distinct 'name_active' per year, but the number must be cumulative. I thought I have found it, but for year 1977, it goes wrong :
These are the measures I used:
- Total name_active = IF(CALCULATE(DISTINCTCOUNT(full_table_detailed[name_active]))=0;0;CALCULATE(DISTINCTCOUNT(full_table_detailed[name_active])))
- Using Quick Measure:
Total name_active running total in YEAR =
CALCULATE(
[Total name_active];
FILTER(
ALLSELECTED('TimeTable'[YEAR]);
ISONORAFTER('TimeTable'[YEAR]; MAX('TimeTable'[YEAR]); DESC)
)
)
I have also tried following measure, but no correct result:
Total name_active running total in YEAR =
CALCULATE(
[Total name_active];
FILTER(
ALL('TimeTable'[YEAR]);
'TimeTable'[YEAR]<= MAX('TimeTable'[YEAR]))
)
Can someone please help?
Thank you in advance.
Kind regards,
Bart
1 Reply
- Greg_DecklerCommunity Champion
See if my Time Intelligence The Hard Way Quick Measure helps at all:
If not, post your data in a format that can be copied and pasted. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490