Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi! I have a table with the inventory of my products, and also the sales of each product.
In order to know how much inventory I have left before running out, I calculate how much inventory I have this week, and divide it by how many sales in average I had in the past.
So, the formula is:
Inventory / Average weekly sales
The result is a number that tells me how many weeks I still can sell without needing more inventory. If it is too low I have to get more product, if it is too high, I may consider a price drop.
This is my problem:
To calculate the average weekly sales, I use the sales of the last 4 to 20 weeks (the exact number depends on each product). This is no problem when I want to see my result of the current week. But if I need to checks last week´s, or any other week in the past, I have to select that week on the slicer, and manually select the other pasts weeks manually, this has created many trouble and many errors by humans.
What I need is some way to select in one click 4 to 20 weeks, prior to the week from where I need the result.
For example, I have weeks 1 to 20, I want to calculate the average using the last 4 weeks for each week. If I select “week15” on a slicer, I need to also select week14, week13, week12 and week11. Or if I select “week10” the filter should also include “week9, week8, week7 and week6”.
Any help or ideas on how to do this?
Thanks very much
Solved! Go to Solution.
hi,@palabi
you may try to add a measure to calculate the average using the last 4 weeks for each week like as below:
Last 4 weeks AVERAGE = VAR currWeek = MAX ( StoreSales[Week id] ) RETURN CALCULATE ( AVERAGE(StoreSales[Qty]), FILTER ( ALL( StoreSales ), StoreSales[Week id] IN GENERATESERIES ( MAX ( currWeek - 4, 1 ), currWeek-1, 1 )
Result:
after selecting one week
Here is DEMO ,please try it
Best Regards,
Lin
hi,@palabi
you may try to add a measure to calculate the average using the last 4 weeks for each week like as below:
Last 4 weeks AVERAGE = VAR currWeek = MAX ( StoreSales[Week id] ) RETURN CALCULATE ( AVERAGE(StoreSales[Qty]), FILTER ( ALL( StoreSales ), StoreSales[Week id] IN GENERATESERIES ( MAX ( currWeek - 4, 1 ), currWeek-1, 1 )
Result:
after selecting one week
Here is DEMO ,please try it
Best Regards,
Lin
Thank you very much for your help.
I´m afraid I dont fully understand the solution, but it seems to work!
Let me have a couple of days to learn a little about the functions please.
thanks again!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
115 | |
100 | |
88 | |
69 | |
61 |
User | Count |
---|---|
151 | |
120 | |
103 | |
87 | |
68 |