Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Dynamic rolling chart

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. 

Need Help
  • v-lili6-msft's avatar
    v-lili6-msft
    7 years ago

    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

     

     

     

1 Reply

  • v-lili6-msft's avatar
    v-lili6-msft
    Community Support
    7 years ago

    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