Create 6 months rolling average on week filter

Question

I am trying to create a report, which should give weekly data but also a column for rolling 6 months till the last month and the same period last year.

I am able to calculate the rolling average using the formula below:

6 months rolling =
VAR period_end = 
    CALCULATE(
        MAX('Dimensions'[Month Start Date]),
        FILTER(
            ALL('Dimensions'[Year Week]),
            'Dimensions'[Year Week]=SELECTEDVALUE('Dimensions'[Year Week])
        )
    )

VAR period_till = 
    FIRSTDATE( 
        DATESINPERIOD(
            'Dimensions'[Month Start Date], 
            period_end, 
            -1, 
            MONTH
        )
    )

VAR period_start = 
    FIRSTDATE( 
        DATESINPERIOD(
            'Dimensions'[Month Start Date], 
            period_till, 
            -6, 
            MONTH
        )
    )

RETURN
    CALCULATE(
        SUM(Total_Sales),
        DATESBETWEEN(
            [Month Start Date], 
            period_start, 
            period_till
        )
    )

The data comes up fine but as soon as i put a slicer on the [Year Week], it starts giving the weekly data, rather than Rolling average.

I think i need to use ALL filter but my efforts haven't paid off on it too yet. Appreciate any help on this.

Report structure is like this :

Category

Current_Week_Data

Previous year same week data

difference %

rolling 6 months (this year - previous 6 year 6 months /previous year 6 months)

OscarLar OscarLar · Accepted Answer · 2018-12-13T07:51:24

I think this will do the trick. If we are in Dec (as now) it will sum 'Data'[X] for June-Nov this year as well as last year, and then calculate the fractional change. It will not be affected, calculation wise, by a slicer on [Year Week]

Rolling n month average = 
var n = 7 //number of months looking back
var currentDate = MAX('Calendar'[Date]) // max date in current context
var startDate = EOMONTH(EDATE(currentDate; -n);0) // end date of the 7th month back
var currentDateLY = currentDate-364 // Monday -> Monday, use 365 if date match is more important. Using 365 also removes strange values in the beginning/end of months.
var startDateLY = EOMONTH(EDATE(currentDateLY; -n); 0)

var theDataTY = // the data This Year
CALCULATE(
    SUM('Data'[X]);
    ALL('Calendar'[Year Week]);
    FILTER(
        ALL('Calendar'[Date]);
        'Calendar'[Date] > ( startDate ) && 'Calendar'[Date] <DATE(YEAR(currentDate);MONTH(currentDate);1) // the 6 month interval 
    )
)
var theDataLY = // the data Last Year
CALCULATE(
    SUM('Data'[X]);
    ALL('Calendar'[Year Week]);
    FILTER(
        ALL('Calendar'[Date]);
        'Calendar'[Date] > ( startDateLY ) && 'Calendar'[Date] <DATE(YEAR(currentDateLY);MONTH(currentDateLY);1) // the 6 month period last year
    )
)

return
DIVIDE( // calculate the fractional change
    theDataTY - theDataLY;
    theDataLY;
    0 // returns 0 if fraction is not defined. 
)

I have two tables in my setup: 'Calendar' and 'Data'. There's a 1:* relationship between 'Calendar'[Date] and 'Data'[Date]. Also, I have no doubt that there is a better way of doing this in DAX, this is just how I would have done it.

Hope this helps.

Cheers,

Oscar

Create 6 months rolling average on week filter

1 Answers