Dynamic rolling chart

[Anonymous]

I have a chart, where I would like to show the previous 16 weeks. It seems the dates/calendar table is not registrered as a number value, rather than dates, so the relative date filter is not an option.

Can anyone help me fix this? I'm working in a SSAS live connection, so I feel like my options are limited. 

[v-lili6-msft · Accepted solution]

HI, @Anonymous 

You may try this way like below:

For you are working in a SSAS live connection, all the modeling process need to be done in ssas, so you need to add a week

column for date field.

Then create a measure in power bi like this:

Measure = VAR __LASTWEEK = MAX(Table2[Week])
	RETURN
		SUMX(
			FILTER(ALL(Table2),Table2[Week]<=__LASTWEEK&&Table2[Week]>=__LASTWEEK-3),
			CALCULATE(SUM('Table2'[Qty]))
		)

For example:

Best Regards,

Lin