Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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) |
Solved! Go to 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
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
DAX measure worked. Thanks for your help!!!
User | Count |
---|---|
140 | |
113 | |
104 | |
76 | |
63 |
User | Count |
---|---|
135 | |
126 | |
110 | |
70 | |
61 |