Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to Solution.
Hi @Anonymous,
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
Hi @Anonymous,
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
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?
HI @Anonymous,
Please share the pbix file for test.(it seems like your table contains blank records)
Regards,
Xiaoxin Sheng
Sure, I will do the sample and share within days.
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
User | Count |
---|---|
141 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
123 | |
101 | |
71 | |
61 |