Dynamic Ageing

[Anonymous]

Hi community

I need to create a dynamic ageing based on the date slicer (month-year)

e.g: I have Nov-2020 selected on my silter then I want to see ageing 30,60 and 90 days from Nov-2020 and so on accordinly with the selection made in the filter slicer.

These 30, 60 and 90 need to be grouped in a way that I can use in a graph on the axis.

The data is basic, i have the below as columns: 

Invoice date

outstanding days

Value

So basically my invoice date is linked with my date table that I have Month-year in the filter and I need to age from the filter.

Any help would be very appreciated. thanks!

[MFelix · Accepted solution]

Hi  @Anonymous 

Since you have a relationship between both tables you need to create a new measure for the total value and another one for the groups do the following:

• Create a table with the groups:

Now create the following two measures:

Days Outstanding = 
VAR Days_Outstanding =
    CALCULATE (
        DATEDIFF (
            SELECTEDVALUE ( Invoices[Invoice Date] ),
            MAX ( DimDate[Date] ),
            DAY
        ),
        CROSSFILTER ( DimDate[Date], Invoices[Invoice Date], NONE )
    )
RETURN
    IF ( Days_Outstanding > 0 , Days_Outstanding )



Total Value =
CALCULATE (
    SUM ( Invoices[Value] ),
    CROSSFILTER ( DimDate[Date], Invoices[Invoice Date], NONE ),
    FILTER (
        ALLSELECTED ( Invoices[Invoice Date] ),
        Invoices[Invoice Date] <= MAX ( DimDate[Date] )
            && [Days Outstanding] <= MAX ( 'Grouping'[Maximum] )
            && [Days Outstanding] >= MIN ( 'Grouping'[Minimum] )
    )
)

Now you can use both these measure and the groups table to do the charts and visualizations:

[Anonymous]

Hi Miguel

Thanks for your solution,

it worked very well for me. 

obrigado!