cancel
Showing results for
Did you mean:
Member

## Sales for last 4 weeks in calc column

It is quite tough to do and to explain also so ask if it is not clear.

I would need 2 calc column formulas.

First for calculating Category turnover for Last 4 weeks. For each row it should calculate a sum of current week Category Turnover and 3 previous ones.

Second dor Calculating Category turnover for Last 4 weeks but for stores selling in current week.

Here is a data set (Table1).

 Product Week Week id Store Category Turnover Category Turnover A week1 1 store1 Home 1 10 A week1 1 store2 Home 2 11 A week1 1 store3 Home 3 12 A week1 1 store4 Home 4 13 A week2 2 store1 Home 5 14 A week2 2 store2 Home 6 15 A week3 3 store1 Home 7 16 A week3 3 store2 Home 8 17 A week3 3 store3 Home 9 18 A week4 4 store2 Home 10 19 A week4 4 store3 Home 11 20 A week4 4 store4 Home 12 21 A week5 5 store2 Home 13 22 A week6 6 store2 Home 14 23 A week6 6 store3 Home 15 24 A week6 6 store4 Home 16 25 A week7 7 store2 Home 17 26 A week8 8 store2 Home 18 27 A week8 8 store3 Home 19 28 B week1 1 store3 Garden 2 13 B week1 1 store4 Garden 3 14 B week2 2 store1 Garden 4 15 B week2 2 store2 Garden 5 16 B week3 3 store1 Garden 6 17 B week3 3 store2 Garden 7 18 B week3 3 store3 Garden 8 19 B week4 4 store2 Garden 9 14 B week4 4 store3 Garden 10 15 B week4 4 store4 Garden 11 16 B week5 5 store2 Garden 12 17 B week6 6 store2 Garden 13 18 B week6 6 store3 Garden 14 19 B week6 6 store4 Garden 15 20 B week7 7 store2 Garden 16 21

Examples:

Please note my real data model contains more fact columns, but granularity is the same

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team

## Re: Sales for last 4 weeks in calc column

Hi @Gordon2,

You can try to use below measures to achieve your requirement:

```Last 4 weeks =
VAR currWeek =
MAX ( StoreSales[Week id] )
RETURN
CALCULATE (
SUM ( StoreSales[Category Turnover ] ),
FILTER (
ALLSELECTED ( StoreSales ),
StoreSales[Week id]
IN GENERATESERIES ( MAX ( currWeek - 4, 1 ), currWeek, 1 )
&& StoreSales[Product] IN VALUES ( StoreSales[Product] )
)
)

Last 4 weeks with store in last week =
VAR currWeek =
MAX ( StoreSales[Week id] )
VAR storeList =
CALCULATETABLE (
VALUES ( StoreSales[Store] ),
FILTER (
ALLSELECTED ( StoreSales ),
StoreSales[Week id] = currWeek
&& StoreSales[Product] IN VALUES ( StoreSales[Product] )
)
)
RETURN
CALCULATE (
SUM ( StoreSales[Category Turnover ] ),
FILTER (
ALLSELECTED ( StoreSales ),
StoreSales[Week id]
IN GENERATESERIES ( MAX ( currWeek - 4, 1 ), currWeek, 1 )
&& StoreSales[Product] IN VALUES ( StoreSales[Product] )
&& StoreSales[Store] IN storeList
)
)
```

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

For learning resources/Release notes, please visit: | |
5 REPLIES 5
Community Support Team

## Re: Sales for last 4 weeks in calc column

Hi @Gordon2,

You can try to use below measures to achieve your requirement:

```Last 4 weeks =
VAR currWeek =
MAX ( StoreSales[Week id] )
RETURN
CALCULATE (
SUM ( StoreSales[Category Turnover ] ),
FILTER (
ALLSELECTED ( StoreSales ),
StoreSales[Week id]
IN GENERATESERIES ( MAX ( currWeek - 4, 1 ), currWeek, 1 )
&& StoreSales[Product] IN VALUES ( StoreSales[Product] )
)
)

Last 4 weeks with store in last week =
VAR currWeek =
MAX ( StoreSales[Week id] )
VAR storeList =
CALCULATETABLE (
VALUES ( StoreSales[Store] ),
FILTER (
ALLSELECTED ( StoreSales ),
StoreSales[Week id] = currWeek
&& StoreSales[Product] IN VALUES ( StoreSales[Product] )
)
)
RETURN
CALCULATE (
SUM ( StoreSales[Category Turnover ] ),
FILTER (
ALLSELECTED ( StoreSales ),
StoreSales[Week id]
IN GENERATESERIES ( MAX ( currWeek - 4, 1 ), currWeek, 1 )
&& StoreSales[Product] IN VALUES ( StoreSales[Product] )
&& StoreSales[Store] IN storeList
)
)
```

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

For learning resources/Release notes, please visit: | |
Member

## Re: Sales for last 4 weeks in calc column

Thanks Xiaoxin Sheng

I have tested both on my target data set and got the below error.

You know how to fix it?

Community Support Team

## Re: Sales for last 4 weeks in calc column

HI @Gordon2,

Please share the pbix file for test.(it seems like your table contains blank records)

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

For learning resources/Release notes, please visit: | |
Member

## Re: Sales for last 4 weeks in calc column

Sure, I will do the sample and share within days.

Member

## Re: Sales for last 4 weeks in calc column

Let me do the short summary.

In the initial context described here this measures working. So I marked a correct answer.

For my business problem it is not yet what I can use.

Two reasons:

- non continouse week id in my target data set caused errors in some scenarios when filtering

- my idea was to divide [Last 4 weeks with store in last week] / [Last  weeks]

to get the ratio. So thats I wanted both in calc column. If I divide the two measures it does not work as I expect.

I want it to be work on category level, week level, product etc

Anyway this post can be helpfull for others and the measures too.

Realy appreciate for help. Thanks Xiaoxin Sheng\

I will continue this thread here (hope you can help):

http://community.powerbi.com/t5/Desktop/last-4-week-sales-in-calc-column/m-p/455797#M211214