Aged Debtors

[Marcus3149]

Hi All,

I have a dataset that contains 3.2m rows of accounts receiveable data.  My users would like a slicer/filter that allows them to select the date they'd like to run the report and the result would something similar to below.

Each row in my dataset contains (amongst other columns) the due date, the clearing date (ie. date settled), the customer number and the value of the invoice.  The data to be displayed would be all invoices with a due date prior to the date selected that have no clearing date or a clearing date after the date selected.  We've managed to achieve this with a series of measures and filters  but having issues with the report taking a long time to refresh and when running from the service we get an "out of memory" error if not enough fields are filtered.  I've attached an example file with data in question and our current methodology.  Anyone know how I can achieve the same result in a more efficient manner?

Aged Trial Balance Example Data.pbix

Thanks,

Marcus

[v-danhe-msft]

Hi @Marcus3149,

Could you please tell me if your problem has been solved? If it is, could you please mark the helpful replies as Answered?

Regards,

Daniel He

[v-danhe-msft · Accepted solution]

Hi @Marcus3149,

Based on my research, you could refer to these links:

https://docs.microsoft.com/en-us/power-bi/power-bi-reports-performance

https://www.sqlbi.com/articles/optimizing-dax-expressions-involving-multiple-measures/

Also, you could modify your measure by using  the CALCULATE function with a single filter:

Age 1 - 30 = 
CALCULATE (
    SUM ( 'Transaction Table'[Value] ),
    FILTER (
        'Transaction Table','Transaction Table'[Due Date]<= SELECTEDVALUE ( 'Selection Date'[Selection Date])
        &&
     [WhatIsTheAge] >= 1 && [WhatIsTheAge] < 31 
     &&
     'Transaction Table'[Clearing Date] > SELECTEDVALUE ( 'Selection Date'[Selection Date] )
            || 'Transaction Table'[Clearing Date] = BLANK ()
            )
            )

Hope it could help you.

Regards,

Daniel He

[calerof]

@v-danhe-msft @Marcus3149 

This thread became useless when the link to the pbix file expired, no one can benefit from it now.