Optimize/Calculate Aging of Client Invoices - exceeded the available resources on PowerBI Service

[Lucian]

Hello Experts,

I need your advice in a "classic aging" problem.

Because @AlexisOlson shocked me into another problem offering a far more "short & clean formula" I hope this "related" problem could be solved also. 🤞

This time the main problem is calculating the "age" the client past due invoices at a selected date based on some dynamic grouping. Between 1-30 days, 31-90 days and over 91 days.

So I have defined the AgingGroups table as follows:

Age Group	Sort order	Min	Max
Total	1	0	99999
Not Due	2	0	0
Total Overdue	3	1	99999
Overdue (1-30)	4	1	30
Overdue (31-90)	5	31	90
Overdue (91-99999)	6	91	99999

Because I need all of the information in a single table matrix, I had to define the proper age groups for Total Overdue that should be the SUM of the three overdue intervals (1-30 + 31-90 + 91-99999).

The Not Due should be used for the invoices that are not (yet) due at selected date and the Total should be the Total Overdue + Not Due.

The model consists in 3 related tables (please ignore at the moment the factCustomerTransactions_AgeGroups table as I will explain it later)

dimCustomers - containing the Customer details

factCustomerTransactions - contains all Customer transactions (invoices and payments)

dimDate - the "calendar table" that has no active relationships with the transactions table

So, my first step was to create a measure that calculate how many days is due each invoice based on selected date:

Customer PastDue = 
IF (
    MIN ( factCustomerTransactions[Initial_Entry_Due_Date] )
        >= MAX ( dimDate[Date] )
        || ISBLANK ( MAX ( dimDate[Date] ) ),
    0,
    DATEDIFF (
        MIN ( factCustomerTransactions[Initial_Entry_Due_Date] ),
        MAX ( dimDate[Date] ),
        DAY
    )
)

Then, inspired by some other postings, create a measure that calculate the "aging" per group at the selected date:

Customer Sold per Aging Grup v1 = 
    VAR vSelectedDate =
        MAX ( dimDate[Date] )
    VAR minDueDate =
        MIN ( factCustomerTransactions[Initial_Entry_Due_Date] )
    VAR sumValue =
        CALCULATE (
            SUM ( factCustomerTransactions[Amount_LCY] ),
            factCustomerTransactions[Posting_Date] <= vSelectedDate,
            FILTER (
                factCustomerTransactions,
                COUNTROWS (
                    FILTER (
                        AgingGroups,
                        [Customer PastDue] >= AgingGroups[Min]
                            && [Customer PastDue]<= AgingGroups[Max]
                    )
                ) > 0
            )
        )
    RETURN
        sumValue

And the resulting matrix will work perfectly in the Power BI desktop:

Unfortunately, publishing the report in the PowerBI service would show the error "Visual has exceeded the available resources":

Trying to put the above two measures into a single one and filter inside the following "unique" measure did not help:

Customer Sold per Aging Grup v2 = 
VAR vSelectedDate =
    MAX ( dimDate[Date] )
VAR minDueDate =
    MIN ( factCustomerTransactions[Initial_Entry_Due_Date] )
VAR sumValue =
    CALCULATE (
        SUM ( factCustomerTransactions[Amount_LCY] ),
        factCustomerTransactions[Posting_Date] <= vSelectedDate,
        FILTER (
            factCustomerTransactions,
            COUNTROWS (
                FILTER (
                    AgingGroups,
                    CALCULATE (
                        IF (
                            MIN ( factCustomerTransactions[Initial_Entry_Due_Date] )
                                >= MAX ( dimDate[Date] )
                                || ISBLANK ( MAX ( dimDate[Date] ) ),
                            0,
                            DATEDIFF (
                                MIN ( factCustomerTransactions[Initial_Entry_Due_Date] ),
                                MAX ( dimDate[Date] ),
                                DAY
                            )
                        )
                    ) >= AgingGroups[Min]
                        && CALCULATE (
                            IF (
                                MIN ( factCustomerTransactions[Initial_Entry_Due_Date] )
                                    >= MAX ( dimDate[Date] )
                                    || ISBLANK ( MAX ( dimDate[Date] ) ),
                                0,
                                DATEDIFF (
                                    MIN ( factCustomerTransactions[Initial_Entry_Due_Date] ),
                                    MAX ( dimDate[Date] ),
                                    DAY
                                )
                            )
                        ) <= AgingGroups[Max]
                )
            ) > 0
        )
    )
RETURN
    if(sumValue<-0.001 || sumValue>0.001,sumValue,BLANK())

Finally I had manage to make it work also in the Power BI service by adding the factCustomerTransactions_AgeGroups table that is a copy of the original factCustomerTransactions table, except I had to removel all the columns that were not involved in "aging" calculation:

Customer Sold per Aging Grup v3 = 
VAR sumValue =
    CALCULATE (
        SUM ( factCustomerTransactions_AgeGroups[Amount_LCY] ),
        factCustomerTransactions_AgeGroups[Posting_Date] <= MAX ( dimDate[Date] ),
        FILTER (
            factCustomerTransactions_AgeGroups,
            COUNTROWS (
                FILTER (
                    AgingGroups,
                    CALCULATE (
                        IF (
                            MIN ( factCustomerTransactions_AgeGroups[Initial_Entry_Due_Date] )
                                >= MAX ( dimDate[Date] )
                                || ISBLANK ( MAX ( dimDate[Date] ) ),
                            0,
                            DATEDIFF (
                                MIN ( factCustomerTransactions_AgeGroups[Initial_Entry_Due_Date] ),
                                MAX ( dimDate[Date] ),
                                DAY
                            )
                        )
                    ) >= AgingGroups[Min]
                        && CALCULATE (
                            IF (
                                MIN ( factCustomerTransactions_AgeGroups[Initial_Entry_Due_Date] )
                                    >= MAX ( dimDate[Date] )
                                    || ISBLANK ( MAX ( dimDate[Date] ) ),
                                0,
                                DATEDIFF (
                                    MIN ( factCustomerTransactions_AgeGroups[Initial_Entry_Due_Date] ),
                                    MAX ( dimDate[Date] ),
                                    DAY
                                )
                            )
                        ) <= AgingGroups[Max]
                )
            ) > 0
        )
    )
RETURN
    if(sumValue<-0.001 || sumValue>0.001,sumValue,BLANK())

So, my question is:

Is there any other way to calculate this aging (shorter formula), or is it possible to optimize the [Customer Sold per Aging Grup] measure, without introducing this "light" copy of the transactions table and keep a cleaner/smaller model? Also I should take into consideration that factCustomerTransactions table cannot be "summarized" because will be needed for a "drill trhough" page with more details for each invoice.

Or this approach of creating the "light" table and related it in a one-to-one relationship with the original table should be used as a "best practice"? Because one good side-effect of this method seems to improve the other v1/v2 measures that suddenly start working in the Power BI service. 🤔

If needed, the complete report is available for the next 30 days here: DAX-CustomerAgingFormula.pbix

[Lucian]

Hello @amitchandak , Hello @AlexisOlson 

First I would like to thank both of you for your time spent on my problem.

@amitchandak - I have tried your approach and mapping the "independent table" with my AgingGroups table and then create the measure like this:

Customer Sold per Aging Grup v4 - amitchandak = 
SUMX (
    FILTER (
        VALUES ( factCustomerTransactions[Amount_LCY] ),
        [Customer PastDue] >= MIN ( AgingGroups[Min] )
            && [Customer PastDue] <= MAX ( AgingGroups[Max] )
    ),
    factCustomerTransactions[Amount_LCY]
)

where the [Customer PastDue] is my "original" formula posted in the initial post:

Customer PastDue = 
IF (
    MIN ( factCustomerTransactions[Initial_Entry_Due_Date] )
        >= MAX ( dimDate[Date] )
        || ISBLANK ( MAX ( dimDate[Date] ) ),
    0,
    DATEDIFF (
        MIN ( factCustomerTransactions[Initial_Entry_Due_Date] ),
        MAX ( dimDate[Date] ),
        DAY
    )
)

Probably I did not fully understand your example - because this formula seems that does not "react" on the "selected date" slicer which is an "before" type selector.

If you could help me adjusting the formula to take this into account it may work. 

@AlexisOlson - I'm amazed again on how short the formula could be, except the fact that does not work for "Not Due" cases. 😢

It works perfectly for Overdue "bins" and even for "Total Overdue", but because of "missing" part with "Not Due" also the "Grand total" (Not Due + Total Due) does not work either.

Do you think you could find a "trick" that would make Not Due to work? Because, this is part of my report requirement and I could not "forget about" it. 😁

Just to "recap" both of your aproaches, I have attached 2 snapshots taken at 2 different dates:

Unfortunately neither approach will not calculate properly until the "Total" that should be the same as the "Customer Balance at Date".

Any other suggestions?

Kind Regards,

Lucian

[AlexisOlson]

You don't need a "trick" so much as a special case. Just replace vSelectedDate - AgeGroupMin with IF ( AgeGroupMin = 0, BLANK(), vSelectedDate - AgeGroupMin):

Customer Sold per Age Group = 
VAR AgeGroupMin = SELECTEDVALUE ( AgingGroups[Min] )
VAR AgeGroupMax = SELECTEDVALUE ( AgingGroups[Max] )
VAR vSelectedDate = MAX ( dimDate[Date] )
VAR DateWindow =
    DATESBETWEEN (
        dimdate[Date],
        vSelectedDate - AgeGroupMax,
        IF ( AgeGroupMin = 0, BLANK(), vSelectedDate - AgeGroupMin)
    )
VAR sumValue =
    CALCULATE (
        SUM ( factCustomerTransactions[Amount_LCY] ),
        factCustomerTransactions[Posting_Date] <= vSelectedDate,
        factCustomerTransactions[Initial_Entry_Due_Date] IN DateWindow
    )
RETURN
    IF ( ABS ( sumValue ) > 0.001, sumValue )

A blank inside DATESBETWEEN means it's unbounded on that side and the function uses the latest date in dimDate[Date].

[Lucian]

Wow, @AlexisOlson you amaze me again how with just a small trick everything works perfect! 🎉

And another BIG WOW because this formula is working on the Power BI Service - no error message and without that "extra table".

Now you showed me again that I have a LOT to learn regarding DAX. 😁

I still have one more question: Why this measure will sort "differently" than my "bad formula"?

Even if the matrix is sorted descending by this measure, it seems that instead of sorting over the Total column is sorting on the Not Due column.

Kind Regards,

Lucian

[AlexisOlson · Accepted solution]

Yeah, it should be much more efficient (the query should take less than 100ms where it was previously ~5000ms) this way so the Service should have no problems.

The sorting isn't done on any particular one of those columns but rather on the ordering you'd get for the row total:

For the row total, the AgeGroupMin and AgeGroupMax variables are blank (since there isn't a single value), so DateWindow becomes

DATESBETWEEN ( vSelectedDate - BLANK(), BLANK() )

which is the same range as in the Not Due case (since BLANK() = 0 evaluates as True).

If you want this to act the same as Total instead, you can add a check of the blank case:

Customer Sold per Age Group = 
VAR AgeGroupMin = SELECTEDVALUE ( AgingGroups[Min] )
VAR AgeGroupMax = SELECTEDVALUE ( AgingGroups[Max] )
VAR vSelectedDate = MAX ( dimDate[Date] )
VAR DateWindow =
    DATESBETWEEN (
        dimdate[Date],
        IF ( ISBLANK ( AgeGroupMax ), BLANK(), vSelectedDate - AgeGroupMax ),
        IF ( AgeGroupMin = 0, BLANK(), vSelectedDate - AgeGroupMin)
    )
VAR sumValue =
    CALCULATE (
        SUM ( factCustomerTransactions[Amount_LCY] ),
        factCustomerTransactions[Posting_Date] <= vSelectedDate,
        factCustomerTransactions[Initial_Entry_Due_Date] IN DateWindow
    )
RETURN
    IF ( ABS ( sumValue ) > 0.001, sumValue )

[Lucian]

Hello @AlexisOlson ,

As usual, I'm speachless, and now I realize how little I about DAX  🙄

This is the expected result and I don't know how could I thank you soooo much 🙏

After weeks of digging in the wrong direction, you gave me the best and optimezed answer in less than a day... so what can I say other than - RESPECT! 

Kind Regards,

Lucian

[AlexisOlson]

You're welcome. Teaching is the best way to truly learn something and I've learned a ton from answering thousands of real-life challenges people like you have shared.

[AlexisOlson]

I don't think it will work for the Not Due case, but structuring your measure like this should be orders of magnitude more efficient:

Customer Sold per Age Group =
VAR AgeGroupMin = SELECTEDVALUE ( AgingGroups[Min] )
VAR AgeGroupMax = SELECTEDVALUE ( AgingGroups[Max] )
VAR vSelectedDate = MAX ( dimDate[Date] )
VAR DateWindow =
    DATESBETWEEN (
        dimdate[Date],
        vSelectedDate - AgeGroupMax,
        vSelectedDate - AgeGroupMin
    )
VAR sumValue =
    CALCULATE (
        SUM ( factCustomerTransactions[Amount_LCY] ),
        factCustomerTransactions[Posting_Date] <= vSelectedDate,
        factCustomerTransactions[Initial_Entry_Due_Date] IN DateWindow
    )
RETURN
    IF ( ABS ( sumValue ) > 0.001, sumValue )

[amitchandak]

@Lucian , I have done dynamic bucket in the example blog. You need to have a group by in table factCustomerTransactions( primary key), which is needed only when you create a date based on selected date( On slicer) , An independent bucket table

see if this approach can help

Dynamic Segmentation Bucketing Binning
https://community.powerbi.com/t5/Quick-Measures-Gallery/Dynamic-Segmentation-Bucketing-Binning/m-p/1...

Dynamic Segmentation, Bucketing or Binning: https://youtu.be/CuczXPj0N-k