Forum Discussion
Solved
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+ da...
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 CountOfAccountsInSelectedCategoriesAnd 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
daXtremeSolution 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 CountOfAccountsInSelectedCategoriesAnd 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.