Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

4 weeks cumulate and evolution

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

Need Help
  • v-yadongf-msft's avatar
    v-yadongf-msft
    3 years ago

    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.

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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

  • v-yadongf-msft's avatar
    v-yadongf-msft
    Icon for Community Support rankCommunity Support
    3 years ago

    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.