Two Linked Tables, Calculating Rolling Y/Y but Filter Not Working

[misterbao]

Hello,

I'm trying to doing a rolling 4 period Y/Y calculation and the only way I've figured out to do so is the following, however when I insert a slicer for region from one table my calc does not seem to be linked and doesn't filter the values. I'm thinking it's how my Value SUM formula is set up.

Also, if someone has a better way to do a rolling Y/Y calculation then by all means suggest a better approach. The results I want to achieve is in the Excel file in the Expected Results tab.

Excel File 

PBI File 

[v-lili6-msft]

hi @misterbao 

First, you should know that:

1. Calculation column/table not support dynamic changed based on filter or slicer.
2. Measure can be affected by filter/slicer, so you can use it to get dynamic summary result.

https://www.sqlbi.com/articles/calculated-columns-and-measures-in-dax/

Your [Rolling] is a calculated column, so it couldn't be affected by slicer.

Second, from your sample I see that your data is calculated rolling by 364 days not one year.

So just adjust your report as below:

Rolling = SUM('Fact'[Value])

Rolling YoY% =

var _last364running=CALCULATE(CALCULATE(SUM('Fact'[Value]), DATEADD('Date'[Date], -364, DAY)),

    FILTER(

        ALLSELECTED('Date'[Date]),

        ISONORAFTER('Date'[Date], MAX('Date'[Date]), DESC)))

return

DIVIDE(SUM('Fact'[Value])-_last364running,_last364running)

And here is sample pbix file, please try it.

Regards,

Lin

[misterbao]

Hi @v-lili6-msft,

Thanks for your response! Definitely some progress to the solution, however it's missing one final element which is the rolling 4 prior periods in the next rows of calculations. If you open up the formula in the rows after, it's taking the sum of the prior 4 periods and taking a year over year % with the similar SUM of the 4 prior weeks of last year. This is the part where I can't seem to figure out. Our first Y/Y % of 1213% matches which is off to a good start, but once you apply the actual rolling SUM of the prior periods that's where my expected results differs from yours. I feel this is so close to the finish line and maybe it's just a slight tweak to the measures to add in last 4 periods but I can't seem to figure it. 

So I was able to replicate what Lin's formula did in my Excel file and it's taking a rolling of last year indefinitely instead of just 4 periods. I only want to take 4 current periods over 4 prior year periods.

Lin's formula:

[misterbao · Accepted solution]

Alright I figured it out. I applied the two following measures and it gives me to exactly what I expect from my manual Excel formula. For anybody out there that is in a similar situation here are the measures I used:

This measure keeps a running 4 week SUM.

This measure calculates a year over year % of the current year running 4 week SUM over the prior year running 4 week SUM.

Thanks all that have contributed to this thread. Much appreciated!

[RobbeVL]

Hi,

I think what you are looking for is a Running total.

The following seemed to work in your sample dataset:

Value running total in Date = 
CALCULATE(
	SUM('Fact'[Value]);
	FILTER(
		ALLSELECTED('Date'[Date]);
		ISONORAFTER('Date'[Date]; MAX('Date'[Date]); DESC)
	)
)

[misterbao]

Thanks for the response, Robbe. I created a new column and inserted a column as you suggested but the running total does not seem to be a rolling of the previous 4 periods.

I entered it as a new measure as I was curious and couldn't get the same values as my rolling past 4 period formula as well.