Aged Debtor Group - Customer Balance

DAX DAX Calculations
1

Hello Everyone,

I’ve stumbled onto an issue with my dax formula. I’m hoping someone can assist me in figuring out what I could be doing wrong.

I’m working on creating a Customer Remaining Balance report. Essentially I was going off the Aged Trial Balance demo from the Financial Reporting videos. Only this report, I need to show overdue balances.

I created an “Aged Debtor Group” table, like so;
image

To calculate the Days left, I used the following DAX formula;

Days Left = 
IF
(
    AND([Invoice Date] < [Selected Date], [Remaining Balance] <> 0),
    DATEDIFF([Selected Date], [Due Date], DAY),
    BLANK()
)

For the remaining balance, I used the following DAX formula;

Remaining Balance = 
SUM
(
    'Customer Balance'[Remaining Amount]
)

And then, for the Balance Due Per Group, I used;

Balance Due Per Group = 
CALCULATE
(
    [Remaining Balance],
    FILTER('Customer Balance',
        COUNTROWS(
            FILTER('Aged Debtor Groups',
                [Days Left] <= 'Aged Debtor Groups'[Min] &&
                [Days Left] >= 'Aged Debtor Groups'[Max])) > 0  ))

The issue I’m facing is that I can’t get the 1 -30 days group to show up in my Matrix table. I have tried different variations in those formulas, and some would give the same results, but no matter what I try, I can’t get the first group to show up.

matrix_table
matrix_table886×328 21.9 KB

The normal table shows days less than 30 based on the selected date; I’m just not sure what I’m missing here to get it to appear in my Debtor group/matrix table.

matrix_table_2
matrix_table_21011×408 43.3 KB

Any help getting the 1 - 30 Days to show up would be greatly appreciated. :smiley:

Thanks,

Brad

Customer Balance - Copy.pbix (226.2 KB)

2

Hi ,

I think the problem lie here in the && condition you put

It should be [Day left]>=min && [Day left]<=max .

For example 10>=1 && 30<=30 so it fall on first group 1-30 .

As per your data the max date diff is 19 as i find out in report:
image
So evert thing fall under first category.

Hope i understand your query well and provide you a solution for the same let me know incase i miss anything i attached the pbix which conation the sol also for your help.
Customer Balance - Copy.pbix (226.3 KB)

3

Hi Anurag,

Thank you for the reply. That was how I initially had the DAX formula. The issue is that when I switch the operators like that, I get the Aged Group from 1 - 30 days, but then it doesn’t filter and display the other three Aged Groups (30 - 60, 61 - 90, and 90+).

I believe I found out what the issue was, my Aged Debtor Group table, and how I had it set up.
image

The outcome I wanted to achieve was to display all current invoices which aren’t overdue (within the selected date) and then display overdue ones (based on the selected date).

Silly me realized I needed to change up how I had my Aged Debtor Group table, and all I needed to do was to change it like so;
image

After that, my report worked as intended, at least from what I can tell. I will give it to accounting, and they will tell me otherwise :stuck_out_tongue_closed_eyes:

Thanks again for the fast reply and for taking the time to review my report. I really do appreciate that :blush:

Regards,

Brad

Attached is the pbix file in case you are curious or if anyone else has a similar issue.

Customer Balance - Copy.pbix (233.2 KB)