Rolling average best and worst performance

[curiouscookie]

Hi there, I'm having difficulty figuring the dax formula to create a rolling 12 month best performance and rolling 12 month worst performance.  The purpose is to create upper and lower bounds in my expense forecast to see if my rolling 12 average is trending towards "best in class" performance or trending towards "worst in class".

I have actuals for the last 24 months or so and forecast for the remainder of year, based on my knowledge of future events and trending.  I also have multiple products so I want to be able to click the product and the averages will change accordingly.  I have learned how to create the DAX measure for rolling 12.   But I can't figure out how to create upper and lower bounds.

Any other suggestions on how else I can define upper and lower bounds would be great too!  Here is the file:

https://drive.google.com/file/d/1kSXB4zictVOSq8UAfHZM97CdkoIWkBt9/view?usp=sharing 

[OwenAuger]

@curiouscookie Glad that worked!

Is your follow-up question referring to my measures above or some other measures you had written?

Could you post the DAX code where it wasn't behaving as expected?

In general, CALCULATETABLE returns a table (first argument) with filters applied (2nd arguments onwards). In my DAX code I was using it to return a table with Month/Sales values with the date filter altered using DATESINPERIOD, which was an intermediate step in the calculation.

I can't think of a reason why CALCULATETABLE would affect whether the measure works in TOPN ASC vs DESC specifically, but it could be something to do with the context where the function was used.

One thing to watch out for is that (blank) values are equivalent to zero for ranking purposes, so there might be a situation where the "lowest" values are (blank) which gives an unexpected result, but not sure if that is relevant in this case.

[Ashish_Mathur]

Hi,

The description in your post does not bear any semblence to the formulas you have written in rows 5 and 6 of your Excel workbook.  In your post, you have alluded to rolling 12 months best and worst performance.  However, in cell M5, the formula calculates the "average actual sale of the best 6 months in the rolling 12 months period".

I am confused about what you want.  Please clarify.

[OwenAuger · Accepted solution]

Hi @curiouscookie 

Here is an example of how I would set up these calculations in Power BI. Sample PBIX attached.

1. I loaded your sample data into a Sales table, transformed so that there is a Type column containing "Actual Sales" or "Forecast Sales". I also created dummy products, "Product A" corresponding to your original data, and "Product B" inflated by 10%.
   

   

2. Created a Date table with relationship with Sales table.
3. Created measures as follows. Note that due to some of the rules of your calculations (only computing if enough months are available, and freezing the best/worst values after the end of actuals), the measures are a bit more complex than your run-of-the-mill rolling measures:

Sales Amount = 
SUM ( Sales[Sales] )

R12 = 
VAR NumMonths = 12
VAR GlobalMinDate = 
    CALCULATE ( MIN ( Sales[Date] ), REMOVEFILTERS() )
VAR ThresholdDate = 
    EOMONTH ( GlobalMinDate, NumMonths - 1 ) // End of month 11 months after GlobalMinDate
VAR MaxDateFiltered = 
    MAX ( 'Date'[Date] )
RETURN
    IF (
        MaxDateFiltered >= ThresholdDate,
        CALCULATE ( 
            AVERAGEX ( 
                VALUES ( 'Date'[End of Month] ),
                [Sales Amount]
            ),
            DATESINPERIOD ( 'Date'[Date], MaxDateFiltered, -12, MONTH )            
        )
    )

Best performance in last 12 months = 
VAR N = 6
VAR NumMonths = 12
VAR ActualMinDate = 
    CALCULATE ( MIN ( Sales[Date] ), REMOVEFILTERS(), Sales[Type] = "Actual Sales" )
VAR ActualMaxDateEOM = 
    EOMONTH ( CALCULATE ( MAX ( Sales[Date] ), REMOVEFILTERS(), Sales[Type] = "Actual Sales" ), 0)
VAR ThresholdDate = 
    EOMONTH ( ActualMinDate, NumMonths - 1 ) // End of month (NumMonths-1) months after ActualMinDate
VAR MaxDateFiltered = 
    MAX ( 'Date'[Date] )
VAR MaxDateForCalc = 
    MIN ( MaxDateFiltered, ActualMaxDateEOM )
RETURN
    IF (
        MaxDateForCalc >= ThresholdDate,
        CALCULATE ( 
            VAR MonthsSales = 
                CALCULATETABLE (
                    ADDCOLUMNS ( 
                        VALUES ( 'Date'[End of Month] ),
                        "@SalesAmount",[Sales Amount]
                    ),
                    DATESINPERIOD ( 'Date'[Date], MaxDateForCalc, -NumMonths, MONTH )
                )
            RETURN
                AVERAGEX (
                    TOPN ( N, MonthsSales, [@SalesAmount], DESC ),
                    [@SalesAmount]
                )
        )
    )

Worst performance in last 12 months Base = 
VAR N = 6
VAR NumMonths = 12
VAR ActualMinDate = 
    CALCULATE ( MIN ( Sales[Date] ), REMOVEFILTERS(), Sales[Type] = "Actual Sales" )
VAR ActualMaxDateEOM = 
    EOMONTH ( CALCULATE ( MAX ( Sales[Date] ), REMOVEFILTERS(), Sales[Type] = "Actual Sales" ), 0 )
VAR ThresholdDate = 
    EOMONTH ( ActualMinDate, NumMonths - 1 ) // End of month (NumMonths-1) months after ActualMinDate
VAR MaxDateFiltered = 
    MAX ( 'Date'[Date] )
VAR MaxDateForCalc = 
    MIN ( MaxDateFiltered, ActualMaxDateEOM )
RETURN
    IF (
        MaxDateForCalc >= ThresholdDate,
        CALCULATE ( 
            VAR MonthsSales = 
                CALCULATETABLE (
                    ADDCOLUMNS ( 
                        VALUES ( 'Date'[End of Month] ),
                        "@SalesAmount",[Sales Amount]
                    ),
                    DATESINPERIOD ( 'Date'[Date], MaxDateForCalc, -NumMonths, MONTH )
                )
            RETURN
                AVERAGEX (
                    TOPN ( N, MonthsSales, [@SalesAmount], ASC ),
                    [@SalesAmount]
                )
        )
    )

[curiouscookie]

@OwenAuger Thank you so much - it works!!

I was wondering if you would mind giving a tid bit of info on CALCULATETABLE.   I was thinking I can use the CALCULATE, AVERAGEX, TOPN, DATESINPERIOD.     This works for "best performance (topN DESC)" but the "worst performance (topN ASC)" doesn't work until I include the CALCULATETABLE.   Curious what is the power of CALCULATETABLE in this context?