Accumulated Balance multiply by month last currency rate

[Digger]

Context:

I am working with a Power BI report where I need to calculate a column Accumulated_Balance_4_LB_Last using the following DAX formula:

Accumulated_Balance_by_currency_rate using the following DAX formula:

Accumulated_Balance_by_currency_rate = DIVIDE([Accumulated_Balance], [Last_currency_rate])
Where:
Accumulated_Balance is calculated as:

• Accumulated_Balance = CALCULATE( [Balance], FILTER( ALL('__Calendar'[Date]), '__Calendar'[Date] <= MAX('__Calendar'[Date]) ) )

• Last_currency_rate is calculated using LASTNONBLANKVALUE:

  Last_currency_rate = LASTNONBLANKVALUE(__Calendar[Date], MAX(Table[Currency_rate.amount_2]))

Additionally, Balance is calculated as:

Balance =

VAR Sum_Debit_1 = CALCULATE( SUM(Table[Value]), Table[Col] = "D", Table2[Col] = "String" ) VAR Sum_Credit_1 = CALCULATE( SUM(Table[Value]), Table[Col] = "C", Table2[Col] = "String" ) VAR Total_1 = Sum_Debit_1 - Sum_Credit_1 VAR Sum_Debit_2 = CALCULATE( SUM(Table[Value]), Table[Col] = "D", Table2[Col] <> "String" ) VAR Sum_Credit_2 = CALCULATE( SUM(Table[Value]), Table[Col] = "C", Table2[Col] <> "String" ) VAR Total_2 = Sum_Debit_2 - Sum_Credit_2 RETURN Total_1 - Total_2

Expected Result:

For a specific row:

• Balance = 99,666.30

• Last_currency_rate = 11.34

• Expected Accumulated_Balance:

  99,666.30 / 11.34 ​=8,788.92

Issue:

Instead of getting 8,788.92, the column Accumulated_Balance is showing 20,047.12.

Troubleshooting Done:

1. Checked Balance values – It seems to return different values in different contexts.
2. Tested dividing manually – The expected division holds true, but the measure does not return the expected result.
3. Possible Issue with LASTNONBLANKVALUE – It might be returning an unexpected value due to context differences.

Question:

• Could there be a context issue with LASTNONBLANKVALUE, causing Balance to return an unexpected value?
• How can I ensure Balance always picks the correct corresponding value for each row?
• What would be the best way to debug and fix this issue?

Any help would be greatly appreciated! 🚀

[v-pagayam-msft]

Hi @Digger ,

We are following up once again regarding your query. Could you please confirm if the issue has been resolved through the support ticket with Microsoft?

If the issue has been resolved, we kindly request you to share the resolution or key insights here to help others in the community. If we don’t hear back, we’ll go ahead and close this thread.

Should you need further assistance in the future, we encourage you to reach out via the Microsoft Fabric Community Forum and create a new thread. We’ll be happy to help.

Thank you.

Regards,
Pallavi.

[v-pagayam-msft]

Hi @Digger ,
Could you please confirm if the issue has been resolved after raising a support case? If a solution has been found, it would be greatly appreciated if you could share your insights with the community. This would be helpful for other members who may encounter similar issues.
Thank you for your understanding and assistance.

[Digger]

@v-pagayam-msftYes, I confirm that the issues were never even addressed and nobody helped. Thank you for your unhelpful community. ISSUE NOT RESOLVED!!!

[v-pagayam-msft]

Hi @Digger ,
May I ask if you have gotten this issue resolved?
If it is solved, please mark the helpful reply or share your solution and accept it as solution, it will be helpful for other members of the community who have similar problems as yours to solve it faster.

Thank you for being a part of the Microsoft Fabric Community Forum!

[v-pagayam-msft]

Hi @Digger ,

I wanted to check in on your situation regarding the issue. Have you resolved it? If you have, please consider marking the reply that helped you or sharing your solution and accept it as solution. It would be greatly appreciated by others in the community who may have the same question.

Thank you for being a part of the Microsoft Fabric Community Forum!

[Digger]

@v-pagayam-msft no. Nobody helps. Noone can solve this issue

[v-pagayam-msft]

Hi @Digger   ,
Thank you for your response. I appreciate your patience. If the issue persists, you may consider raising a support ticket for further assistance.
To raise a support ticket for Fabric and Power BI, kindly follow the steps outlined in the following guide:
How to create a Fabric and Power BI Support ticket - Power BI | Microsoft Learn

If this post helps, please give us Kudos and consider Accept it as a solution to help the other members find it more quickly.

Thank you for your cooperation. Have a great day.

[Digger]

@v-pagayam-msft have you tryed ever create support ticket? It is mission imposible.

[v-pagayam-msft]

Hi @Digger ,

You can create a support ticket as per Microsoft's official documentation. It states that Fabric users and Power BI Pro users can submit support tickets for Power BI and Fabric-related issues.

Please refer to the documentation here for a step-by-step guide on how to create a support ticket with Microsoft.

Thank you for being a valued member in Microsoft Fabric Community Forum!

[DataNinja777]

Hi @Digger ,

My recommendation is instead of using two separate columns for debits and credits as your ERP outputs using absolute values, converting them into a single column balance using unpivot Power Query will simplify the formula.  

By unpivoting the debit and credit columns into a single "Balance" column in Power Query, the DAX formula can be significantly simplified. Instead of having to separately sum debits and credits while subtracting them, the balance calculation can be reduced to a straightforward sum.

After unpivoting the ERP data in Power Query so that there is a single "Balance" column, the measure for calculating balance would then simply be:

Balance = 
CALCULATE(
    SUM(Table[Balance]),
    __Calendar[Date] <= MAX(__Calendar[Date])
)

This eliminates the need for multiple CALCULATE statements and separate conditions for debit and credit, making the measure much cleaner and more efficient.

With this, the accumulated balance can also be simplified:

Accumulated_Balance = 
CALCULATE(
    [Balance],
    FILTER(
        ALLSELECTED('__Calendar'[Date]),  
        '__Calendar'[Date] <= MAX('__Calendar'[Date])
    )
)

And applying the currency rate would remain the same:

Accumulated_Balance_by_currency_rate = 
DIVIDE([Accumulated_Balance], [Last_currency_rate])

This approach improves performance and readability, reducing unnecessary complexity while ensuring the calculation remains accurate.

Best regards,

[Digger]

t.y. but i cant change table structure

[v-pagayam-msft]

Hi @Digger   ,

Thank you for reaching out to the Microsoft Fabric Community Forum about the issue you are encountering.

I personally thank @DataNinja777   for the prompt response.
The Balance measure sums debits and credits, which can be affected by row context and filters. Context transitions can lead to unexpected results.

• Use REMOVEFILTERS or ALL to remove any unwanted filters and ensure the Balance measure behaves correctly.

If this post helps, please give us Kudos and consider Accept it as a solution to help the other members find it more quickly.

[Digger]

@v-pagayam-msft have you read my post? or just spaming for cudoes?

[v-pagayam-msft]

Hi @Digger ,
Sorry for the inconvenience. I tried to recreate the scenario locally where I obtained the expected accumulated balance
I am also inculding the pbix for your reference.

Could there be a context issue with LASTNONBLANKVALUE causing Balance to return an unexpected value?
Yes, it can behave differently based on context. If the context changes, it may lead to discrepancies in your calculations.

How can I ensure Balance always picks the correct value for each row?
Manage the row and filter context carefully. Use CALCULATE to modify the context of LASTNONBLANKVALUE also debug by analyzing context differences and evaluating intermediate values.

Hope this helps.Thank you for being a valued member in Fabric Community Forum!