The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event! Join us in Stockholm, Sweden from September 24-27, 2024.
2-for-1 sale on June 20 only!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
Afternoon,
I'm trying to build a guage to compare the sum of last week's sessions with the average sum of the past two weeks.
For Example:
Week One: 10 Page Hits
Week Two: 20 Page Hits
So; Last Week's Sum of Page hit's is 10.
And the Last Two Week's Sum's Average by Week is (10+20/2) = 15
I've given it a try using dateadd, datesinperiod, datesbetween and fiddling around with some EARLIER or Date[DateKey]-28 for example; but since I need it on a rolling basis; we can't simply choose between a specific start and end date.
If I try to do:
Past Two Weeks Sum Average = Calculate(Sum(NumberofSessions), DATESINPERIOD (NumberofSessionsDateTable, TODAY(), -14, DAY))
It returns me of course the sum of the past two weeks.
I've thought of maybe breaking it up and using measures and some addition and subtraction to make it work; but I get stuck with the same issue of how for example to choose between -7 and -14 day interval on a rolling basis.
I'd prefer not to have to dynamically filter a bunch of tables to do this as I want to compare over a few different periods (enough so that it would take ages).
Any thoughts would be greatly appreciated.
Last 2 Weeks = CALCULATE ( [Sumsessions13], FILTER ( ALL ( 'SessionsWkIndex' ), 'SessionsWkIndex'[Week Index] >= MAX ( 'SessionsWkIndex'[Week Index] ) -2 && 'SessionsWkIndex'[Week Index] <= MAX ( 'SessionsWkIndex'[Week Index] ) ) )
We can adapt this for Dayindex.
I'm just wondering if someone could walk me through using this.
Would I do this for days and simply add a -7 and a -14 in the place of the -2 and after the second bracket after <=MAX. And then Calc the averages this way?
Doing:
Last 2 Weeks = CALCULATE ( [Sumsessions13], FILTER ( ALL ( 'SessionsWkIndex' ), 'SessionsWkIndex'[Week Index] >= MAX ( 'SessionsWkIndex'[Week Index] ) -7 && 'SessionsWkIndex'[Week Index] <= MAX ( 'SessionsWkIndex'[Week Index] ) -14 ) )
Returns a blank. Hmmm
= CALCULATE( AVERAGEX(VALUES(Sales[Week No]), Sales[Sum of Sales]) ,Sales[Week No] <= VALUES(Sales[Week No]) && Sales[Week No] > VALUES(Sales[Week No])-2 ) , )
I'm not sure if I take out the if function if it will make it dynamic.
Then the concern is when i convert to days. Would I need to create a rolling group week?
I feel I'm missing something here quite basic.
Side Note; If I stay in weeks, when the dashboard updates daily won't it simply click over to the next week so for 6 days a week the data will be massivly skewed when comparing past 7 days vs past 14 days (as two groups of 7 day weeks).
An idea I'm throwing around now is the moving average type feeling.
So calculate two measures, one a sum of the past seven days, another a sum of the past 14 days.
Then use a countrows function and divide the rows to recieve a daily average.
Then multiply the values by seven.
Seems convoluted, I'm really hoping someone has a better idea.
Or alternativly we get around not using the Week Index number option by sliding it back a week so its always comparing full week index numbers. Was I wrong with my earlier assumption? How would i slide it back a week anyway...
I'm feeling the best option is to somehow group the periods I want to consider (such as 7 days, 14 days, 30 days rolling) and then take the average of their sum's.
Thoughts?
I have done some work on this.
I have assumed you have a table of week numbers along with Sales in each week.
1. Created a table as follows:
WeekNum | Sales |
1 | 189 |
2 | 266 |
3 | 174 |
4 | 240 |
5 | 378 |
6 | 300 |
7 | 258 |
8 | 490 |
9 | 539 |
10 | 81 |
2. Created a measure
RunTot:=CALCULATE (
SUM ( 'Table2'[Sales]),
FILTER ( ALL ( Table2 ), Table2[WeekNum] >= MAX ( 'Table2'[WeekNum] ) - 2 &&Table2[WeekNum] <= MAX ( 'Table2'[WeekNum] ) -1 )
)
This creates the total for the previous two weeks prior to the current week row.
If you want to compute for current week and past week change the measure as
RunTot:=CALCULATE (
SUM ( 'Table2'[Sales]),
FILTER ( ALL ( Table2 ), Table2[WeekNum] >= MAX ( 'Table2'[WeekNum] ) - 1 &&Table2[WeekNum] <= MAX ( 'Table2'[WeekNum] ) )
)
3. Created a measure to find the number of past data rows available in the data against each week
TotRows:=CALCULATE (
COUNTROWS( 'Table2'),
FILTER ( ALL ( Table2 ), Table2[WeekNum] >= MAX ( 'Table2'[WeekNum] ) - 2 &&Table2[WeekNum] <= MAX ( 'Table2'[WeekNum] ) - 1)
)
This is similar to the RunTot Measure except that instead of Sum([Sales]) it counts the rows satisfying the week condition.
4. Now for the Average of the past weeks
created a measure
AverageSales:=Divide([RunTot],[TotRows])
5. The final out put is as follows :
Row Labels | Sum of Sales | RunTot | TotRows | AverageSales |
1 | 189 | |||
2 | 266 | 189 | 1 | 189 |
3 | 174 | 455 | 2 | 227.5 |
4 | 240 | 440 | 2 | 220 |
5 | 378 | 414 | 2 | 207 |
6 | 300 | 618 | 2 | 309 |
7 | 258 | 678 | 2 | 339 |
8 | 490 | 558 | 2 | 279 |
9 | 539 | 748 | 2 | 374 |
10 | 81 | 1029 | 2 | 514.5 |
Grand Total | 2915 | 1029 | 2 | 514.5 |
6. There are few challenges you have. How are you numbering the Week Numbers ? What happens when a new year starts how do you nuber the weeks ?
Hope this meets your requirement.
If so please accept this as solution and also give Kudos.
Cheers
CheenuSing
@CheenuSing 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).
Current 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
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 ...
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
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
Hi @shalini
I don't get you clear. Can you please elaborate.
Cheers
CheenuSing
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
102 | |
95 | |
80 | |
62 | |
56 |
User | Count |
---|---|
256 | |
120 | |
112 | |
83 | |
71 |