Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get 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

Reply
Leggies
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

daXtreme_0-1659903547873.png

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:

daXtreme_1-1659903808159.png

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.

View solution in original post

6 REPLIES 6
Leggies
Frequent Visitor

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 

daXtreme_0-1659903547873.png

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:

daXtreme_1-1659903808159.png

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.

Leggies
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:
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. 

Leggies_0-1659866269531.png

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.

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

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

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

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