topic Re: Difference in cumulative balance between power bi and excel in DAX Commands and Tips

Difference in cumulative balance between power bi and excel

RW23 — Mon, 20 Apr 2026 14:20:02 GMT

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])

)

Re: Difference in cumulative balance between power bi and excel

krishnakanth240 — Mon, 20 Apr 2026 17:12:54 GMT

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])))

Re: Difference in cumulative balance between power bi and excel

RW23 — Mon, 20 Apr 2026 18:08:58 GMT

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

Re: Difference in cumulative balance between power bi and excel

krishnakanth240 — Tue, 21 Apr 2026 01:09:54 GMT

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.

Re: Difference in cumulative balance between power bi and excel

v-sgandrathi — Mon, 27 Apr 2026 10:20:41 GMT

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.

Re: Difference in cumulative balance between power bi and excel

v-sgandrathi — Thu, 30 Apr 2026 05:27:45 GMT

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.

Re: Difference in cumulative balance between power bi and excel

RDewi — Fri, 01 May 2026 10:29:43 GMT

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])

)

Re: Difference in cumulative balance between power bi and excel

v-sgandrathi — Mon, 18 May 2026 08:00:48 GMT

Hi RW23,

Thanks for the update. Since the issue only occurs on the last day and previous suggestions haven't fully resolved it, could you share a small sample of the data in text or table format, along with the expected result from Excel and the result from Power BI? Also, please confirm if the Posting Date column includes any time values. This will help us better understand if the issue is related to filter context, duplicate dates, time values, or model relationships.

How to provide sample data in the Power BI Forum - Microsoft Fabric Community

Thank you.