Forum Discussion

BBertels's avatar
BBertels
Frequent Visitor
8 years ago

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