Dynamic Ageing

Question

Hi community&nbsp;I need to create a dynamic ageing based on the date slicer (month-year)&nbsp;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.&nbsp;These 30, 60 and 90 need to be grouped in a way that I can use in a graph on the axis.&nbsp;The data is basic, i have the below as columns:&nbsp;Invoice dateoutstanding daysValue&nbsp;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.&nbsp;Any help would be very appreciated. thanks!&nbsp;

mfelix · Accepted Answer

Hi&nbsp;&nbsp;Anonymous&nbsp;
&nbsp;
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:
&nbsp;

Create a table with the groups:

&nbsp;
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 &gt; 0 , Days_Outstanding )

Total Value =
CALCULATE (
    SUM ( Invoices[Value] ),
    CROSSFILTER ( DimDate[Date], Invoices[Invoice Date], NONE ),
    FILTER (
        ALLSELECTED ( Invoices[Invoice Date] ),
        Invoices[Invoice Date] &lt;= MAX ( DimDate[Date] )
            &amp;&amp; [Days Outstanding] &lt;= MAX ( 'Grouping'[Maximum] )
            &amp;&amp; [Days Outstanding] &gt;= MIN ( 'Grouping'[Minimum] )
    )
)
&nbsp;
Now you can use both these measure and the groups table to do the charts and visualizations:

&nbsp;
&nbsp;

Forum Discussion

Dynamic Ageing

2 Replies

Related content

Age Bins

stock ageing

Calculating receivable aging dynamically

stock ageing

Dynamic Accounts Receivable Aging