Dynamic summation of "previous"weeks for revenues

[Anonymous]

Hi Folks

this might be easy for you, but I am struggling to get it done.

I have a table with the following columns

- Date

- Calendar Week

- SKU

- Customer

- Sales

I want to add a dynamic column which would be the Sales of the last 12 weeks of each line at the same level of details. Some would call that Rolling sum, but I can't figure out how to do that...

Here is the result I want to achieve: 

Date	CalWeek	SKU	Customer	Sales	Sales_Prev12Week
01012020	202001	ABDC	Apple	100	500
02012020	202001	ABDC	Apple	423	500
01022020	202005	ABDC	Apple	899	1023 (500+100+423)

The 12 weeks calculation is a bit tricky, so I have a "time" dimension, on which for every week, I calculate the Week -12 (taking into account the year changes)

So I was thinking of some thing like: 

Sales_Prev12Weeks = 

Calculate (  sum('myTable'[Sales]), Filter ('myTable', AND( 'myTable'[CalWeek] < RELATED('time'[CALWEEK]), 'myTable'[CalWeek] > RELATED('time'[CALWEEK]) -12))

But it does not work...

Am I missing something ?

[v-yuta-msft]

@Anonymous ,

You do not need to create addional 'time' table, you can create a measure using dax like pattern below:

Sales_Prev12Weeks =
VAR Current_Week =
    MAX ( myTable[CalWeek] )
VAR Twelve_Weeks_Before = Current_Week - 12
RETURN
    CALCULATE (
        SUM ( 'myTable'[Sales] ),
        FILTER (
            'myTable',
            'myTable'[CalWeek] < Current_Week
                && 'myTable'[CalWeek] > Twelve_Weeks_Before
        )
    )

Community Support Team _ Jimmy Tao

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

[Anonymous]

Hi @v-yuta-msft 

Thanks for the feedback...

I have tried that before but results are not aligned....

The current week -12 is not straight forward because of year changes, but I managed to get it calculated and added to every line of the table. So basically, what I am doing is what you proposed, but results are really off the track...

I am trying to figure out why, but at least, it confirms me this is the way more experimented people would do 😉

Thanks

PY

[amitchandak · Accepted solution]

@Anonymous ,

I created it based on week rank.

Last 12 week Sales = CALCULATE(SUM(Sales[Net Sales]),FILTER(all('Date'),'Date'[Week Rank]>=max('Date'[Week Rank])-12 && 'Date'[Week Rank]<=max('Date'[Week Rank])))

Please find the attached file, There quite a few there. Refer to last 12 weeks.

There is a chance that week start; what you want to use might not the same as what I used. You can change week number, to get the same.

[Anonymous]

@amitchandak, thanks for the update!

I have tried and so far it looks to be the way to go...

I am not totally sure so far as I would need to create much more data, but will probably try later.

By the way, coming from another world, this approach is not very intuitive...

[Anonymous]

Hi @Anonymous 

Columns can not be dynamic they are get loaded at first load only.

you need to create measure for dynamic requirement.

Thanks & regards,
Pravin Wattamwar
www.linkedin.com/in/pravin-p-wattamwar

If I resolve your problem Mark it as a solution and give kudos.

[Anonymous]

Hi @Anonymous 

Thanks for the feedback.

The "column" Sales_12Weeks is a measure ....

PY