cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Gordon2 Member
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).

 

ProductWeekWeek idStoreCategoryTurnoverCategory Turnover 
Aweek1              1store1Home1                                  10
Aweek1              1store2Home2                                  11
Aweek1              1store3Home3                                  12
Aweek1              1store4Home4                                  13
Aweek2              2store1Home5                                  14
Aweek2              2store2Home6                                  15
Aweek3              3store1Home7                                  16
Aweek3              3store2Home8                                  17
Aweek3              3store3Home9                                  18
Aweek4              4store2Home10                                  19
Aweek4              4store3Home11                                  20
Aweek4              4store4Home12                                  21
Aweek5              5store2Home13                                  22
Aweek6              6store2Home14                                  23
Aweek6              6store3Home15                                  24
Aweek6              6store4Home16                                  25
Aweek7              7store2Home17                                  26
Aweek8              8store2Home18                                  27
Aweek8              8store3Home19                                  28
Bweek1              1store3Garden2                                  13
Bweek1              1store4Garden3                                  14
Bweek2              2store1Garden4                                  15
Bweek2              2store2Garden5                                  16
Bweek3              3store1Garden6                                  17
Bweek3              3store2Garden7                                  18
Bweek3              3store3Garden8                                  19
Bweek4              4store2Garden9                                  14
Bweek4              4store3Garden10                                  15
Bweek4              4store4Garden11                                  16
Bweek5              5store2Garden12                                  17
Bweek6              6store2Garden13                                  18
Bweek6              6store3Garden14                                  19
Bweek6              6store4Garden15                                  20
Bweek7              7store2Garden16                                  21

 

Examples:

Capture.PNG

 

Capture1.PNG

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

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
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
        )
    )

25.PNG

 

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
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
        )
    )

25.PNG

 

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: | |
Gordon2 Member
Member

Re: Sales for last 4 weeks in calc column

Thanks Xiaoxin Sheng

 

I appreciate your help.

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

You know how to fix it?

 

Capture5.PNG

Community Support Team
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: | |
Gordon2 Member
Member

Re: Sales for last 4 weeks in calc column

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

Gordon2 Member
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