Forum Discussion

baadshah's avatar
baadshah
Frequent Visitor
3 years ago
Solved

Clients Running Total with Distinct Count

Hi


I have a data set with client sales and i want to aggregate a running total of the distinct count of clients. So in sample data 2020 Q1 we started of with 1 client and everytime a new client joined it increase the count for each period in the new viz. I've seen few examples on DAX forum but couldnt get it to work for my case. Thanks

 

Sample Data: 

PeriodClient Name
2020 Q1 Client 1 
2020 Q1Client 1
2020 Q2Client 2
2020 Q3Client 3
2020 Q4 Client 4
2021 Q1 Client 5
2021 Q1 Client 5

 

End Result:

 

PeriodRunning Distinct Count
2020 Q11
2020 Q22
2020 Q33
2020 Q4 4
2021 Q1 5
DAX
Distinct Counts
Running Totals
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi baadshah ,

     

    According to your description, here are my steps you can follow as a solution.

    (1) My test data is the same as yours.

    (2) We can create a measure.

    Running Distinct Count = 
VAR CurrentPeriod = MAX('Sample Data'[Period])
RETURN
CALCULATE(
    DISTINCTCOUNT('Sample Data'[Client Name]),
    FILTER(
        ALLSELECTED('Sample Data'),
        'Sample Data'[Period] <= CurrentPeriod
    )
)

    (3) Then the result is as follows.

    Best Regards,

    Neeko Tang

    If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly. 

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi baadshah ,

     

    According to your description, here are my steps you can follow as a solution.

    (1) My test data is the same as yours.

    (2) We can create a measure.

    Running Distinct Count = 
VAR CurrentPeriod = MAX('Sample Data'[Period])
RETURN
CALCULATE(
    DISTINCTCOUNT('Sample Data'[Client Name]),
    FILTER(
        ALLSELECTED('Sample Data'),
        'Sample Data'[Period] <= CurrentPeriod
    )
)

    (3) Then the result is as follows.

    Best Regards,

    Neeko Tang

    If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly.