topic Re: Measure with historical aging data in DAX Commands and Tips

Measure with historical aging data

Leggies — Wed, 03 Aug 2022 08:16:44 GMT

Hi community,

Over the last few days I have been struggling with the following issue:

For my company I need to divide the accounts receivable by aging (<30 days, 30-60 days, 60-90 days, 90+ days). So far so good this is an pretty straightforward thing to do in a calculated column. However, I also need the accounts receivable by age from last week in the same table in my report.

In this case the calculated column approach doesn’t work anymore and I will have to start working with measures. I got as far as trying to create a measure per age group looking as follows:

Open 0-30 =

VAR lastday = MIN(CalendarTable[EOWEEK])

RETURN CALCULATE(SUM('AccountMutations'[AR]), DATESINPERIOD('AccountMutations'[Date], lastday-30, lastday, day))

Open 30-60 =

VAR lastday = MIN(CalendarTable[EOWEEK])

RETURN CALCULATE(SUM('AccountMutations'[AR]), DATESINPERIOD('AccountMutations'[Date], lastday-60, lastday-30, day))

Etc.

Unfortunately, when I put this in a report table per week this seems to not give me the results I want but in stead gives me the overall difference in AR over the past week (ignoring my aging filter on the measure). I have been looking everywhere and found some ideas for dynamic aging with slicers but nowhere a solution to put the aging of this week and last week next to eachother in a report table.

Can anyone think of why this does not work and how to fix it? All creative solutions are super welcome!

Re: Measure with historical aging data

Leggies — Sun, 07 Aug 2022 10:00:15 GMT

UPDATE:
Still didnt manage to figure this out. Here a bit more description:
I basically want to do what is done in this video:
https://www.youtube.com/watch?v=5_KDJGPjMnI
except for in stead of a slicer i need the values for this week and last weeks Accounts Receivable.
So the table in circled RED in the image for this week AND for last week.

Currently I can do the grouping in calculated columns and make two separate report tables with the grouped data. However I want to have 1 pivot table with the grouping and the AR values of this week AND the AR value sof last week.
Who can help me please? Let me know if I need to explain more or more info is needed.

THANK YOUU!!

Re: Measure with historical aging data

daXtreme — Sun, 07 Aug 2022 11:02:38 GMT

Hi Leggies

This does not seem to be overly complex judging by the video...

Can you please supply example data? I don't want to type everything by hand. There does not need to be a lot of it, just enough to demonstrate that it works correctly. The data can be fake, of course, but feel free to supply whatever you want. As long as it's relevant it's OK.

By the way, it's better to provide a link to a shared file (can be Excel, csv, tab-delim... you name it) instead of pasting something in here. The tables that this site renders are just awful and horrible as if they'd been designed in the XIX century. I wish the administrators of the site provided a better environment for us... and this awful styling has been going on and on and on... What a shame.

Thanks.

Re: Measure with historical aging data

Leggies — Sun, 07 Aug 2022 18:42:45 GMT

Hey daXtreme ,

Thank you for your reaction. I thought it shouldnt be too complicated but got completely stuck. Please find some sample data in the link below (hope its enough, please let me know). so would like to end up with a table with 0-30,30-60 etc categories and the AR that belongs to those per week. So how much AR total was between 0-30 days old this week and which were 0-30 days old last week etc.

https://docs.google.com/spreadsheets/d/1xjos3l8Kqz1ndRW9MUqiu9n_APkEoj0ra9t3xQqU5jc/edit?usp=sharing

Re: Measure with historical aging data

daXtreme — Sun, 07 Aug 2022 20:24:26 GMT

DEFINE MEASURE Accounts[AR Total] = SUM( Accounts[AR] ) MEASURE 'Account Categories'[# Accounts (This Week)] = var CountOfAccountsInSelectedCategories = sumx( 'Account Categories', var LowerEndInclusive = 'Account Categories'[Lower End] var UpperEndExclusive = 'Account Categories'[Upper End] var CountOfAccounts = CALCULATE( DISTINCTCOUNT( Accounts[Relation] ), keepfilters( LowerEndInclusive <= Accounts[Days Open this week] ), keepfilters( Accounts[Days Open this week] < UpperEndExclusive ) ) return CountOfAccounts ) return CountOfAccountsInSelectedCategories MEASURE 'Account Categories'[AR Total (Last Week)] = var ARTotal = sumx( 'Account Categories', var LowerEndInclusive = 'Account Categories'[Lower End] var UpperEndExclusive = 'Account Categories'[Upper End] var AccountsTotal = CALCULATE( [AR Total], keepfilters( LowerEndInclusive <= Accounts[Days Open last week] ), keepfilters( Accounts[Days Open last week] < UpperEndExclusive ) ) return AccountsTotal ) return ARTotal MEASURE 'Account Categories'[AR Total (This Week)] = var ARTotal = sumx( 'Account Categories', var LowerEndInclusive = 'Account Categories'[Lower End] var UpperEndExclusive = 'Account Categories'[Upper End] var AccountsTotal = CALCULATE( [AR Total], keepfilters( LowerEndInclusive <= Accounts[Days Open this week] ), keepfilters( Accounts[Days Open this week] < UpperEndExclusive ) ) return AccountsTotal ) return ARTotal MEASURE 'Account Categories'[# Accounts (Last Week)] = var CountOfAccountsInSelectedCategories = sumx( 'Account Categories', var LowerEndInclusive = 'Account Categories'[Lower End] var UpperEndExclusive = 'Account Categories'[Upper End] var CountOfAccounts = CALCULATE( DISTINCTCOUNT( Accounts[Relation] ), keepfilters( LowerEndInclusive <= Accounts[Days Open last week] ), keepfilters( Accounts[Days Open last week] < UpperEndExclusive ) ) return CountOfAccounts ) return CountOfAccountsInSelectedCategories

And here's the Account Categories table:

Note the intervals are left-closed-right-open. You can have it the other way round but you'll have to adjust the measures. It's easy.

Re: Measure with historical aging data

daXtreme — Sun, 07 Aug 2022 20:25:35 GMT

By the way... The Accounts Categories table has no relationship with any other table. It's free-standing, disconnected.

Re: Measure with historical aging data

Leggies — Mon, 08 Aug 2022 07:12:06 GMT

daXtreme THANK YOU!!!
This is def the way to go! I am really close to getting the right amounts now! Super gratefull, thanks!