I need a DAX for my output column

[LB-Tech]

Hello,
I Need the measure for the Output column value. I have mentioned the calculation logic for the output column. Note: For months December, January, February 2024 i have no credit and debit , so it should be blank.

[v-denglli-msft · Accepted solution]

Thanks for the reply from rajendraongole1, please allow me to provide another insight.
Hi @LB-Tech ,

Please try the following DAX.

Output = 
 VAR _tb = FILTER(ALL('Table'),'Table'[Month]<=MAX('Table'[Month]))
 RETURN
SUMX(_tb,'Table'[Debit]-'Table'[Credit])+20000

Please see the attached pbix for reference.

Best Regards,
Dengliang Li

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

[Ashish_Mathur · Accepted solution]

Hi,

I have solved a similar question in the attached files.

Hope this helps.

[v-denglli-msft · Accepted solution]

Thanks for the reply from rajendraongole1, please allow me to provide another insight.
Hi @LB-Tech ,

Please try the following DAX.

Output = 
 VAR _tb = FILTER(ALL('Table'),'Table'[Month]<=MAX('Table'[Month]))
 RETURN
SUMX(_tb,'Table'[Debit]-'Table'[Credit])+20000

Please see the attached pbix for reference.

Best Regards,
Dengliang Li

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

[rajendraongole1]

Hi @LB-Tech - can you try the below measure:

Output =
VAR CurrentMonth = MAX('Table'[Month]) -- Current row's month
VAR OpeningBalance = 20000 -- Your initial balance
VAR PreviousBalance =
CALCULATE(
[Output],
FILTER(
ALL('Table'[Month]),
'Table'[Month] < CurrentMonth
)
)
VAR Debit = SUM('Table'[Debit])
VAR Credit = SUM('Table'[Credit])
RETURN
IF(
ISBLANK(Debit) && ISBLANK(Credit),
BLANK(), -- Leave blank when both Debit and Credit are blank
COALESCE(PreviousBalance, OpeningBalance) + Debit - Credit
)

[LB-Tech]

Thank you. But this not giving correct value.