Forum Discussion
Post ProdigyCompare Last Week's Sum and Past Two Week's Sum Average
Post ProdigyAnonymous I think that's very very good progress. It doesn't do what I'd like, but its definitly got the code to build from.
I'd like to for example, say I have 14 days of data. 14 amounts of sales, one per day.
I would like to find the sum of sales for each week. We can do this through using a week Number and summing the sales (would appreciate the code for this if you're so inclined), this doesn't achieve what I'd like to do in the rolling sense, but we can work on that another time.
Then be able to create a measure which then averages different sums of weeks. So One measure I'd like would be to sum average the past two weeks; one would be the past 6 weeks.
The two ideas I'm working from atm are
1. Downgrade what I'd like and just do averages using the datesinperiod filter.
2. Play with PowerQuery and row filtering to create some tables with filtered setups (admittedly not a fan as this will be very very very labourous).
Post ProdigyCurrent thinking;
1. Take the sum of the past two weeks using the datesinperiod function
2. Since I want to find the Sum of each week, for the past two weeks, divide the Sum measure by 2. This produces a rolling average weekly sum over the period I want to consider.
3. Average this function over the past 14 days using the datesinperiod function to find a rolling average of my average in 2. which gives me the average weekly sum per week for the period under consideration.
It's quite late at night, so I'm not sure if I'm making any sense or whether I've just found averages on averages.
Hi Shalini,
If you try the following formula for RunTot , it should work
RunTot = CALCULATE (
SUM ( 'Data'[Qty]),
FILTER ( ALLEXCEPT( Data,Data[Category]), Data[WeekNum] >= MAX ( 'Data'[WeekNum] ) - 4 &&Data[WeekNum] <= MAX ( 'Data'[WeekNum] ))
)Replace Data with your table name.
When you put Category in the table output ensure the Category column is sorted in the ascending order of category.
Let me know if you face any issues.
If it works please accept this as a solution and also give KUDOS.
Cheers
CheenuSing
Hi
Can you share some sample data and the output desired for me to do a solution.
Cheers
CheenuSing
thanks for the above calculation - i am using the same formula but how to break it down say by "Category"
below table works fine when used as calulated measure for over all table values. (average 4 weeks)
Row Labels Qty last 4 weeks Avg Qty last 4 weeks qty 13 761368 190342 761368 14 1223186 496138.5 1984554 15 373046 589400 2357600 16 1323902 920375.5 3681502 17 676313 899111.75 3596447 18 623604 749216.25 2996865 19 1494204 1029505.75 4118023 20 1374620 1042185.25 4168741 21 1044666 1134273.5 4537094 22 1042296 1238946.5 4955786 23 1323468 1196262.5 4785050 but when i use the "Category" breakdown option its not calculating per Category instead shows teh same values as above. Looks like i need to change teh formual a bit but not sure how.
below is the 2nd table:
Year Category WeekNum Qty Last 4 Wks Avg qty Last 4 Wks qty 2017 5066-9616 13 336 190342 761368 2017 5066-9616 14 1344 496138.5 1984554 2017 5066-9616 15 1456 589400 2357600 2017 5066-9616 17 1792 899111.75 3596447 2017 5066-9616 19 896 1029505.75 4118023 2017 5066-9616 21 1120 1134273.5 4537094 2017 5066-9616 22 784 1238946.5 4955786 2017 5066-9616 23 1904 1196262.5 4785050 but the actual result i need is as below:
Row Labels Qty last 4wks avg qty last 4wks qty 13 336 84 336 14 1344 420 1680 15 1456 784 3136 17 1792 1148 4592 19 896 672 2688 21 1120 504 2016 22 784 700 2800 23 1904 952 3808 please help ...
thank you CheenuSing!! That worked !!!
:)
hi CheenuSing,
Just a quick check.. can i use the same logic to add a formula into "Add column" instead of a measure? if so what will it be ?
and also is there a way to combine tables (Table1+Table2+Table3) that you see in the attached screen shot into Table 4, cause i have calculated measues on these and i have filtered the weeks that i need accordengly and now i just want to combine these into 1 table in the visualization page. and this new table 4 should allow me to add more measures.
is there a way to do this?
ideally i need to achive as below:
Part Avaialble Qty Last 4 Wks Shipment Last 4 Wks Avg Shipment 6 Wks DDS 6 Wks Avg DDS Part a 39,636 23,360 5,840 43,003 7,167 Part b 0 0 0 0 0 Part c 53,929 19,674 4,919 37,230 6,205 Part d 0 0 0 0 0 is this the correct way or is there a better way to do this?
Thanks
