cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## Calculate balance from debit and credit foe each customer

I am trying to calculate Total balance from debit and credit columns. but my formula is not adding debit values and subtracting credit values it's showing same value in balance. it should be customer and date wise (date from older to newer). I will show one customer at a time in my report on user selection based.

1 ACCEPTED SOLUTION
Super User

@aqsasaleem

Try this measure:

``````Balance =
CALCULATE (
SUM ( cl_resultset[md_debit] ) - SUM ( cl_resultset[md_credit] ),
WINDOW (
1,
ABS,
0,
REL,
SUMMARIZE (
ALLSELECTED ( cl_resultset ),
cl_resultset[md_date],
cl_resultset[refernce number]
)
)
)
``````

Did I answer your question? Mark my post as a solution! and hit thumbs up
6 REPLIES 6
Super User

@aqsasaleem

``````Balance =
CALCULATE ( [Debit Balance], tablename[date] <= MAX ( tablename[date] ) )
- CALCULATE ( [Credit Balance], tablename[date] <= MAX ( tablename[date] ) )``````

Did I answer your question? Mark my post as a solution! and hit thumbs up
Frequent Visitor

@Fowmy I used your formula it's working but where dates are same (Transactions on same date) it is grouping values. any solution?

Formula is

Balance =
CALCULATE (
CALCULATE ( SUM ( cl_resultset[md_debit] )
- CALCULATE ( SUM ( cl_resultset[md_credit] ))),
FILTER ( ALL ( cl_resultset[md_date], cl_resultset[cl_type], cl_resultset[md_name], cl_resultset[md_description]), cl_resultset[md_date] <= MAX ( cl_resultset[md_date] ) )
)

and result is

Super User

@aqsasaleem

Try this measure:

``````Balance =
CALCULATE (
SUM ( cl_resultset[md_debit] ) - SUM ( cl_resultset[md_credit] ),
WINDOW (
1,
ABS,
0,
REL,
SUMMARIZE (
ALLSELECTED ( cl_resultset ),
cl_resultset[md_date],
cl_resultset[refernce number]
)
)
)
``````

Did I answer your question? Mark my post as a solution! and hit thumbs up
Frequent Visitor

@Fowmy Thank you so much it's working 😊

Super User

Hi, @aqsasaleem

try below

``````Balance =
var a = MAX ( tablename[date] )
var b = CALCULATE ( [Debit Balance], tablename[date] < MAX ( tablename[date] ) )+a
var c = CALCULATE ( [Credit Balance], tablename[date] <= MAX ( tablename[date] ) )+a
return
b-c``````
Frequent Visitor

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.