Calculate Daily Cumulative Value for multiple clients

[Anonymous]

Hi, I am very new to Power BI Desktop and trying to write one DAX formula to calculate Cumulative charges value which can be use in Waterfall visualization. I can do this in Excel but not sure how can I make this happen in Power BI.

I have a list of clients with their Daily Charges and payment posted. I need to calculate Daily change (Highlighted in Green below).

For Client 1 Daily changes on 04th April would be Value of 3rd - Value of 4th April and daily changes on 05th April would be Value of 4th - Value of 5th April. Same for rest of the clients. Please help me with this, I am trying to figure this out past 2 weeks.

[v-yulgu-msft]

Hi @Anonymous,

In this scenario, you should create two calculated columns to get the desired daily cumulative value.

Rank =
RANKX (
    'Daily Cumulative Value',
    'Daily Cumulative Value'[Report Date],
    ,
    ASC,
    DENSE
)

Daily charge =
'Daily Cumulative Value'[Charges]
    - LOOKUPVALUE (
        'Daily Cumulative Value'[Charges],
        'Daily Cumulative Value'[Practice], 'Daily Cumulative Value'[Practice],
        'Daily Cumulative Value'[Rank], 'Daily Cumulative Value'[Rank] - 1
    )

Best regards,
Yuliana Gu

[Anonymous]

Hello Yuliana, Thank you so much for your help. Column for Rank is working fine  but formula of Daily Charges flashing an error.

Formula I used:

Daily Charges =
'Financial'[Charges]
    - LOOKUPVALUE(
        'Financial'[Charges],
        'Financial'[Practice], Financial[Practice],
        Financial[Rank], 'Financial'[Rank] - 1
    )

Getting following error : 'A table of multiple values was supplied where a single value was expected'.

Just to update you that there are more than 400 Client name in original raw report. Thanks

[v-yulgu-msft]

Hi @Anonymous,

In your source table, does this scenario exist? The same client ID has multiple data rows in a day?
Client1   April 03,2017

Client1   April 03,2017

Client2   April 03,2017

Regards,
Yuliana Gu

[Anonymous]

Hello @v-yulgu-msft, Every Client ID will have only one row in a day. No multiple entries for any client.

[v-yulgu-msft · Accepted solution]

Hi @Anonymous,

Please modify the Rank column as below for a test.

Rank =
RANKX (
    FILTER (
        'Daily Cumulative Value',
        'Daily Cumulative Value'[Practice]
            = EARLIER ( 'Daily Cumulative Value'[Practice] )
    ),
    'Daily Cumulative Value'[Report Date],
    ,
    ASC,
    DENSE
)

Best regards,
Yuliana Gu

[Anonymous]

@v-yulgu-msft You are genius. This is working absolutely fine. Thanks