Running total of a measure between two dates

[Brij]

Hi,

I am having some difficulty getting the running total of a measure (distinct count of an ID column) for two dates selected on the slicer.

Background

I have an ID (say SubID) column of table A that I need to know the distinct count for the period selected on the date slicer (coming from Date dimention table which is in relationship with table A). For this, I have already created a simple measure as per below: It is being displayed on the card visual.

DCount of SubID = Calculate(DistinctCount(SubID))

Requirements

Now I need to visualize the same subID count on the colum chart that shows distinct count of SubID (above measure) along with a line chart that shows running total. For example I have below distinct count of SubID for the selected period and desire result that I want on the line chart (runnig total);

Date	Distinct Count of SubID	Desire result on line chart (Running total of selected period)
5th Feb 2024	50	50
6th Feb 2024	60	110
7th Feb 2024	100	210
8th Feb 2024	80	290
9th Feb 2024	90	380

Can someone please help me to get this working.

Thank you,

Brij

[Brij]

Hi again,

I have made some progress in getting the running total of a distinct count of SubID.

However, running total for 23rd Jan should show 90 (42 + 48), instead, it is showing 75 which is the distinct count of SubID for this period!

Can someone please help me to rectify this issue?

Any help would be appreciated.

Brij

[Ashish_Mathur]

Hi,

Why do you have time stamps on the X-axis?  Are you required?  Share the download link of the PBI file.

[lbendlin]

You are trying to add snapshots rather than finding the true distinct count.  Use SUMMARIZECOLUMNS over your dates, calculate the distinct count for each day and then SUMX it all together.

But the result will not really make much sense.  Your 75 is more useful, in my opinion.

[Brij]

Hi, Thanks for your quick response. I do agree with you on result 75 is more useful. However, currently business is using old reports and they are having it in such a way that I am trying to get. So have no choice but to fulfill the client's requirements!

Are you able to provide me a sample measure that you have suggested?

I appreciate your quick help.

Brij