cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Sekhar1
Frequent Visitor

Calculate Credit limit running balance

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.

CustCustomer NameCredit LimitCredit Limit Effective Date InvoiceEntry TypeInvoice DateInvoice Amount Due Date Credit Exposure Running BalanceCredit Exposure running balance calculation comments
1202Joe Bloggs375,00001/04/20221IN24/08/202212,90023/09/2022362,100Calculated from  Credit limit - Invoice 1 amount 
1202Joe Bloggs375,00001/04/20222IN24/08/20228,82023/09/2022353,280Calculated from Credit limit - (Invoice 1 and Invoice 2 Amount) 
1202Joe Bloggs375,00001/04/20223IN24/08/20228,82023/09/2022344,460Calculated from Credit limit - (Invoice 1, 2 & 3 Amount) 
1202Joe Bloggs375,00001/04/20224IN26/10/202218,36025/11/2022326,100Calculated from Credit limit - (Invoice 1, 2, 3 & 4 Amount) 
1202Joe Bloggs150,00009/11/20225CR24/11/2022-8,82024/12/2022158,820Credit Limit was changed for Customer from £375000 to £150000
New calc from this row = Credit limit - Invoice 5 amount 
1202Joe Bloggs150,00009/11/20226CR24/11/2022-8,82024/12/2022167,640Calculated from Credit limit - (Invoice 5 & 6 Amount) 
1202Joe Bloggs150,00009/11/20227CR24/11/2022-8,82024/12/2022176,460Calculated from Credit limit - (Invoice 5, 6 & 7  Amount) 
1202Joe Bloggs150,00009/11/20228IN25/11/202248,49225/12/2022127,968Calculated from Credit limit - (Invoice 5, 6, 7 & 8 Amount) 
1202Joe Bloggs150,00009/11/20229IN25/11/202219,52525/12/2022108,443Calculated from Credit limit - (Invoice 5, 6, 7 , 8 & 9 Amount) 
1202Joe Bloggs150,00009/11/202210IN25/11/202211,49125/12/202296,952Calculated from Credit limit - (Invoice 5, 6, 7, 8, 9 & 10  Amount) 
1202Joe Bloggs150,00009/11/202211IN16/12/202290,42415/01/20236,528Calculated from Credit limit - (Invoice 5, 6, 7, 8, 9, 10 & 11 Amount) 
1202Joe Bloggs150,00009/11/202212IN16/12/20225,74715/01/2023781Calculated from Credit limit - (Invoice 5, 6, 7, 8, 9, 10, 11  & 12  Amount) 
1202Joe Bloggs150,00009/11/202213IN16/12/202223,36315/01/2023-22,582Calculated from Credit limit - (Invoice 5, 6, 7, 8, 9, 10, 11, 12  & 13 Amount) 
1 ACCEPTED SOLUTION
grantsamborn
Solution Supplier
Solution Supplier

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

 

View solution in original post

3 REPLIES 3
grantsamborn
Solution Supplier
Solution Supplier

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

 

@grantsamborn 

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

DAX measure worked. Thanks for your help!!! 

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

BizApps LATAM 2023

Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

Power Platform Bootcamp

Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.