Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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!
Solved! Go to Solution.
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.
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
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.
@daXtreme THANK YOU!!!
This is def the way to go! I am really close to getting the right amounts now! Super gratefull, thanks!
By the way... The Accounts Categories table has no relationship with any other table. It's free-standing, disconnected.
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!!
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.
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
User | Count |
---|---|
21 | |
21 | |
21 | |
13 | |
12 |
User | Count |
---|---|
43 | |
28 | |
25 | |
23 | |
22 |