Cumulative Total: I want to take into account all prior dates to the selected date range

[JoyH]

Sample File: https://drive.google.com/file/d/1IXufeou56iMyzzlXH7RBttoJug8daA8k/view?usp=sharing

I am trying to calculate the cumulative value using ALL previous dates but only DISPLAY the last 5 days.  

Current:

Expectation:

I have 3 tables trans, account, date

There is an active relationship on trans.date = date.date

There is an inactive relationship on account.accountcreateddate = date.date.

Account Count = CALCULATE(

                    COUNTROWS(Account),

                    USERELATIONSHIP(Account[AccountCreatedDate],DateTable[Date])

)

From 3/1/24 - 4/29/24 there was 1 account created per day.  

If I look at the running sum without any filters, the running sum of account count on 4/25 was 56

But when I filter the page on DateTable.Date, the runningsum/cumulative count resets to only include that date range. The Transaction Amount is displaying correctly.

Total Cumulative Amount =

    CALCULATE(

        [TotalAmount],

        FILTER(

            ALL('trans'),

            trans[date] <= MAX(trans[date])

        ))

What is the correct DAX for Total Cumulative Account Count?

[PhilipTreacy]

Hi @JoyH 

Download example PBIX file

If you have a Date table you should use an active relationship to it.  I deleted the relationship between trans and the Date table and made the relationship between the Date table and Account active.

I then wrote this

Measure = CALCULATE(COUNTROWS('Account'), FILTER(ALL('Account'), 'Account'[AccountCreatedDate] <= SELECTEDVALUE('Account'[AccountCreatedDate]) ))

You can then apply a Relative filter to the visual to only show the last X number of days 

Regards

Phil

[JoyH]

Thank you for taking the time to reply. 
I need to keep the relationship from trans to DateTable as the transaction date is what most of the report is about. The enduser wants to see the cumulative accounts active during the same date range.

You did give me another idea, however, and that is to count the accounts from the trans table and not the account. They will see the # of accounts that had transactions vs. # of accounts active, but it should be very close.

[v-jianpeng-msft · Accepted solution]

Thanks @PhilipTreacy and @PhilipTreacy 

Hi, @JoyH 

Based on the example PBIX file you provided, I created the following DAX expression:

Total Cumulative Account Count1 = 
VAR _seleteddate =
    SELECTEDVALUE ( DateTable[Date] )
RETURN
    IF (
        ISINSCOPE ( DateTable[Date] ),
        COUNTAX (
            FILTER ( ALL('Account'), 'Account'[AccountCreatedDate] <= _seleteddate ),
            'Account'[account]
        ),
        COUNTA ( Account[account] )
    )

 Here are the results:

I've uploaded the PBIX file I used this time below.

How to Get Your Question Answered Quickly

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards

Jianpeng Li

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

[HotChilli]

I think you want to use the Date dimension in the DAX:

Total Cumulative Account Count = 
    CALCULATE(
        [Account Count],
         DateTable[Date] <= MAX(DateTable[Date])
         )