Difference in cumulative balance between power bi and excel

[RW23]

I have a weird situation where I'm using Power Bi to provide cumulative balances for any given client.  Every day's balance matches to what I did manually in excel except the last day.  Nothing unusal happened on the last day.  The date filter is the same in power bi and excel as I go by posting date.  Excel's balance is the correct one.  Please advise.

This is Power Bi:

This is Excel:

My formula for daily balance in DAX is:

DAILY BALANCE =

CALCULATE(

    SUM(Data[Amount]),

    FILTER(

        ALL('Dateupdated'),

        'Dateupdated'[Date] <= MAX('Dateupdated'[Date])

    )

)

[RDewi]

Hi,

As others have mentioned, it could be due to the use of ALL. You might have data from previous year and that could be picked up since you take away all including years, months, not only dates which shows higher value in PBI compared to Excel on that last row. Try to just take all Dates, change a bit your code to:

DAILY BALANCE =

CALCULATE(

    SUM(Data[Amount]),

    FILTER(

        ALL('Dateupdated'[Date]) --just take Date

        'Dateupdated'[Date] <= MAX('Dateupdated'[Date])

    )

)

[v-sgandrathi]

Hi @RW23,

I wanted to follow up on our previous suggestions regarding the issue. We would love to hear back from you to ensure we can assist you further.

Thank you.

[v-sgandrathi]

Hi @RW23,

Since the discrepancy appears only on the last day, it’s probably not just caused by using ALL(), but rather something specific in the data or filter context. A frequent cause is that the 'Dateupdated'[Date] column might have a time component, which means Power BI could treat different times on the same day as separate entries, while Excel might combine them into a single date. Make sure you’re working with pure dates, for example, by using DATEVALUE. Also, check if there are any multiple or late transactions on the last day, as these can affect only the final cumulative value. Confirm that your visual uses the actual date column and not the date hierarchy, since that can change the context. For a more reliable calculation, try using REMOVEFILTERS() instead of ALL() to better manage context. Finally, compare your total sum with the last cumulative value, if they don’t match, it’s likely a filter or context issue; if they do, it may be related to how the visual groups the data.

Thank you.

[krishnakanth240]

Difference is caused by using ALL('Dateupdated') which removes date filter context and can include extra dates beyond selected range leading to an incorrect final cumulative value. 

You can use ALLSELECTED() where calculation respects applied date filter and matches Excel.

DAILY BALANCE =

CALCULATE(SUM(Data[Amount]),FILTER(ALLSELECTED('Dateupdated'),'Dateupdated'[Date] <= MAX('Dateupdated'[Date])))

[RW23]

Thanks.  That didn't work.  The difference is only on the last day which is strange.  

[krishnakanth240]

Hi @RW23 

Can you share the sample data in text format.

In the Power BI Image, current state of Date column is Hierarchy, please change it to Date by clicking on Down arrow next to the field.