Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Running Total for Count Distinct Measure

I am trying to count distinct values in a by date and create a new measure that calculates the cumulative sum of the distinct counts as the time period progresses: The table on the left is representa...
Need Help
  • MattAllington's avatar
    MattAllington
    6 years ago

    Assuming you have a calendar table

    https://exceleratorbi.com.au/power-pivot-calendar-tables/

    something like this

    Distinct products =DISTINCTCOUNT(data[product])

     

    Running total =CALCULATE(sumx(values(calendar[date]),[Distinct Products]),Filter(All(calendar),calendar[date] <= max(calendar[date])))

     

    use the calendar date column in your visual. 

Anonymous's avatar
Anonymous
Not applicable
6 years ago

HI Anonymous 

 

I used the below to get the running total.

 

CALCULATE (
    SUM ( 'Table'[Count Distinct Product] ),
    ALL ( 'Table' ),
    'Table'[Date] < EARLIER ( 'Table'[Date] )
)