Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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 Sage
Solution Sage

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 Sage
Solution Sage

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.