Dynamic Ageing

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: