cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## Measure with historical aging data

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!

1 ACCEPTED SOLUTION
Solution Sage

``````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.

6 REPLIES 6
Frequent Visitor

Hey

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.

Solution Sage

``````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.

Frequent Visitor

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

Solution Sage

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

Frequent Visitor

UPDATE:
Still didnt manage to figure this out. Here a bit more description:
I basically want to do what is done in this video:
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!!

Solution Sage

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.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors