Calculate Credit limit running balance

[Sekhar1]

Hi,

I have below table from Cust to Due Date Columns. I want to  calculate Credit Exposure Running Balance and result shall be as per Credit exposure running balance column in below table highlighted in RED text.  Credit exposure running balance calculation is done as per comments highlighted in red text in below table. I am having issue because Credit limit changes and unsure how to capture this in DAX calculation.  Can you please advice how to resolve this.

Thanks for your help in advance.

Cust	Customer Name	Credit Limit	Credit Limit Effective Date	Invoice	Entry Type	Invoice Date	Invoice Amount	Due Date	Credit Exposure Running Balance	Credit Exposure running balance calculation comments
1202	Joe Bloggs	375,000	01/04/2022	1	IN	24/08/2022	12,900	23/09/2022	362,100	Calculated from  Credit limit - Invoice 1 amount
1202	Joe Bloggs	375,000	01/04/2022	2	IN	24/08/2022	8,820	23/09/2022	353,280	Calculated from Credit limit - (Invoice 1 and Invoice 2 Amount)
1202	Joe Bloggs	375,000	01/04/2022	3	IN	24/08/2022	8,820	23/09/2022	344,460	Calculated from Credit limit - (Invoice 1, 2 & 3 Amount)
1202	Joe Bloggs	375,000	01/04/2022	4	IN	26/10/2022	18,360	25/11/2022	326,100	Calculated from Credit limit - (Invoice 1, 2, 3 & 4 Amount)
1202	Joe Bloggs	150,000	09/11/2022	5	CR	24/11/2022	-8,820	24/12/2022	158,820	Credit Limit was changed for Customer from £375000 to £150000 New calc from this row = Credit limit - Invoice 5 amount
1202	Joe Bloggs	150,000	09/11/2022	6	CR	24/11/2022	-8,820	24/12/2022	167,640	Calculated from Credit limit - (Invoice 5 & 6 Amount)
1202	Joe Bloggs	150,000	09/11/2022	7	CR	24/11/2022	-8,820	24/12/2022	176,460	Calculated from Credit limit - (Invoice 5, 6 & 7  Amount)
1202	Joe Bloggs	150,000	09/11/2022	8	IN	25/11/2022	48,492	25/12/2022	127,968	Calculated from Credit limit - (Invoice 5, 6, 7 & 8 Amount)
1202	Joe Bloggs	150,000	09/11/2022	9	IN	25/11/2022	19,525	25/12/2022	108,443	Calculated from Credit limit - (Invoice 5, 6, 7 , 8 & 9 Amount)
1202	Joe Bloggs	150,000	09/11/2022	10	IN	25/11/2022	11,491	25/12/2022	96,952	Calculated from Credit limit - (Invoice 5, 6, 7, 8, 9 & 10  Amount)
1202	Joe Bloggs	150,000	09/11/2022	11	IN	16/12/2022	90,424	15/01/2023	6,528	Calculated from Credit limit - (Invoice 5, 6, 7, 8, 9, 10 & 11 Amount)
1202	Joe Bloggs	150,000	09/11/2022	12	IN	16/12/2022	5,747	15/01/2023	781	Calculated from Credit limit - (Invoice 5, 6, 7, 8, 9, 10, 11  & 12  Amount)
1202	Joe Bloggs	150,000	09/11/2022	13	IN	16/12/2022	23,363	15/01/2023	-22,582	Calculated from Credit limit - (Invoice 5, 6, 7, 8, 9, 10, 11, 12  & 13 Amount)

[grantsamborn · Accepted solution]

Hi @Sekhar1 

Maybe try this measure:

zMeasure = 
VAR _Cust = SELECTEDVALUE( 'CreditLimitData'[Cust] )
VAR _InvDt = SELECTEDVALUE( 'CreditLimitData'[Invoice Date] )
VAR _InvNo = SELECTEDVALUE( 'CreditLimitData'[Invoice] )
VAR _Limit = 
    CALCULATE(
        MIN( 'CreditLimitData'[Credit Limit] ),
        'CreditLimitData'[Invoice Date] = _InvDt
    )
VAR _Result =
    CALCULATE(
        _Limit - SUM( 'CreditLimitData'[Invoice Amount] ),
        FILTER(
            ALL( 'CreditLimitData' ),
            'CreditLimitData'[Cust] = _Cust
                && 'CreditLimitData'[Invoice] <= _InvNo
                && 'CreditLimitData'[Credit Limit] = _Limit
        )
    )
RETURN
    _Result

pbix: https://1drv.ms/u/s!AnF6rI36HAVkhPIqB6fqeJHzWvHYlg?e=qiK4po

[Sekhar1]

@grantsamborn 

DAX Measure worked. Thanks for your assistance!!!!!

[Sekhar1]

DAX measure worked. Thanks for your help!!!