Calculate a proxy return rate for last 30 days that applies dynamically when filtering

[Anonymous]

So, I'm working for an ecommerce company. Return rate is a key metric. When building our KPI dashboards we want to incorporate a return rate metric that is adjusted for proxy/estimated returns for current sales. So basically like this:

For daily sales last 30 days (measured backwards from the latest order date in the model) we want to use an estimated return rate rather than the actual return rate since it normally takes up to 30 days from order date for a return to come.

So the return rate would be: Actual returns for any period prior to last 30 days + estimated returns for last 30 days of sales (based on the average return rate 90 days prior to last 30 days).

This needs to apply dynamically over time filters and other dimensions. What I struggle with is that I have managed to create the metric so that it applies correctly on a daily basis, but as soon as I instead filter on a month and the 30 day period is split over month it breaks down.

So first image shows that it works as intended. CY Return_90D% (Sales) calculates a correct average and returns it on each row for the last 90 days. Then I can multiply that metric with actual sales last 30 day and get a proxy return amount.

However, when I add a month filter it breaks like this:

The total is of course still correct, but it will look very strange when we track it. I want the CY Return_90D% (Sales) metric to be able to handle that kind of break.

The CY Return_90D% (Sales) is calculated like this today:

_m CY Return_90D% (Sales) =

VAR LastDateInData = MAXX(ALL(ItemLedgerEntrySales), ItemLedgerEntrySales[orderDate])

VAR ProxyStartDate = LastDateInData - 120

VAR ProxyEndDate = LastDateInData - 30

VAR ReturnAmount90D =

    CALCULATE(

        SUM(ItemLedgerEntrySales[salesAmountActual]),

        ItemLedgerEntrySales[orderDate] >= ProxyStartDate,

        ItemLedgerEntrySales[orderDate] <= ProxyEndDate,

        ItemLedgerEntrySales[documentType] = "Sales Return Receipt",

        REMOVEFILTERS('_t DateTable'[Date])

    )

VAR SalesAmount90D =

    CALCULATE(

        SUM(ItemLedgerEntrySales[salesAmountActual]),

        ItemLedgerEntrySales[orderDate] >= ProxyStartDate,

        ItemLedgerEntrySales[orderDate] <= ProxyEndDate,

        REMOVEFILTERS('_t DateTable'[Date])

    )

VAR ReturnRate90D = DIVIDE(ReturnAmount90D, SalesAmount90D, 0)

RETURN

IF (

    MAX('_t DateTable'[Date]) > LastDateInData - 30,

    ReturnRate90D * -1,

    BLANK()

)

So any ideas for how I can fix this metric to achieve that goal?

[SamWiseOwl · Accepted solution]

Hi @Anonymous 

I'll need to re-read this after a coffee, assuming another helpful soul doesn't step in before then!

Looking at your images I can see a column [Year-Month].

Is this in the Date table?

When you say Calculate( blah, REMOVEFILTERS('_t DateTable'[Date])) you are only removing the filter from the Date column. If [Year-Month] is in the same table it is still filtering your data. Meaning if you have a slicer choosing a month they are now BOTH being applied.

You can use ALL(table or column) to remove internal (rows in the visual) and external (slicers, drillthrough etc) filters.

AllSelected(table or column) only removes interal (rows in the visual).

AllExcept(table, tables or columns) removes all filters on the give table EXCEPT the ones you tell it to keep. So AllExcept(Sales, Region[RegionName]) would return every sale but keep any Region filters.