Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
ElliotP
Post Prodigy
Post Prodigy

Compare Last Week's Sum and Past Two Week's Sum Average

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.

16 REPLIES 16
ElliotP
Post Prodigy
Post Prodigy

Link to source

 

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

http://www.powerpivotpro.com/2011/03/advanced-dax-calculation-doing-a-moving-grouped-average-in-powe...

 

=
	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?

@ElliotP

 

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:

WeekNumSales
1189
2266
3174
4240
5378
6300
7258
8490
9539
1081

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 LabelsSum of SalesRunTotTotRowsAverageSales
1189   
22661891189
31744552227.5
42404402220
53784142207
63006182309
72586782339
84905582279
95397482374
108110292514.5
Grand Total291510292514.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

  

 

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!

@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.

 

@ElliotP

 

Hi

 

Can you share some sample data and the output desired for me to do a solution.

 

Cheers

 

CheenuSing

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!

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 LabelsQtylast 4 weeks Avg Qtylast 4 weeks qty
13761368190342761368
141223186496138.51984554
153730465894002357600
161323902920375.53681502
17676313899111.753596447
18623604749216.252996865
1914942041029505.754118023
2013746201042185.254168741
2110446661134273.54537094
2210422961238946.54955786
2313234681196262.54785050

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:

YearCategoryWeekNumQtyLast 4 Wks Avg qtyLast 4 Wks qty
20175066-961613336190342761368
20175066-9616141344496138.51984554
20175066-96161514565894002357600
20175066-9616171792899111.753596447
20175066-9616198961029505.754118023
20175066-96162111201134273.54537094
20175066-9616227841238946.54955786
20175066-96162319041196262.54785050

 

but the actual  result i need is as below:

Row LabelsQtylast 4wks avg qtylast 4wks qty
1333684336
1413444201680
1514567843136
17179211484592
198966722688
2111205042016
227847002800
2319049523808

 

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

 

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!

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:

PartAvaialble QtyLast 4 Wks ShipmentLast 4 Wks Avg Shipment6 Wks       DDS6 Wks       Avg DDS
Part a39,63623,3605,84043,0037,167
Part b00000
Part c53,92919,6744,91937,2306,205
Part d00000

 

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

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.