4 weeks cumulate and evolution

[Anonymous]

Hello, I have a model without time intelligence.

I have my dimTable with weeks

And I want to have Sales cumulative for last 4 weeks

Then I want to compare for each week sales evolution of 4 last weeks (put it in the chart)

For example we are Week 43, I would like to have the sums ({43,42,41,40}/{42,41,40,39}) - 1 ect.

Could you please suggest the right way to do that?

Thank you

[v-yadongf-msft · Accepted solution]

Hi @Anonymous ,

This is my test table:

Create a new column:

WeekNumber = WEEKNUM('Sheet7'[Date])

Create a measure to calculate Sales cumulative for last 4 week:

Sales cumulative for last 4 weeks = 
var max_week = MAX('Sheet7'[WeekNumber])
return
CALCULATE(SUM('Sheet7'[Sales]), FILTER('Sheet7','Sheet7'[WeekNumber] <= max_week && 'Sheet7'[WeekNumber] >= max_week - 3 ))

Sales cumulative for last 4 weeks -1 = 
var max_week = MAX('Sheet7'[WeekNumber])-1
return
CALCULATE(SUM('Sheet7'[Sales]), FILTER('Sheet7','Sheet7'[WeekNumber] <= max_week && 'Sheet7'[WeekNumber] >= max_week - 3 ))

Measure = DIVIDE([Sales cumulative for last 4 weeks] , [Sales cumulative for last 4 weeks -1]) -1

Is this the result you want?

Best regards,

Yadong Fang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Let's simplify my question : 

We are week 43 - I want to create groups of 4 weeks starting with last available week

So W43,42,41,40 should be group 11, W39,38,37,36 should be group 10 ect.

How would you do that?

Thank you