26 & 52 Week Averages on data set with no dates and multiple criteria

[Anonymous]

Hello Everyone,

I've been working on this for a while now to no avail. I looked up solutions online and came back empty handed.

I have a data set similar to the below table but much larger. The dataset represents weekly metrics for multiple clients going back a few years. Problem is, the data has no datetime - it's split by week and year fields from the source.

My goal is to get the 26-week & 52-week averages for each client as of today for Cost, Quanty and everything else that's measurable in the table. Then the output would be represented in a separate matrix per client like so:

Client          26-week-avgQuantity          26-week-avgCost          52-week-avgQuantity          52-week-avgCost ....

Client 1                  xx                                             xx                                  xx                                          xx

Client 2                  xx                                             xx                                  xx                                          xx

Since i couldn't add a datetime filter (there are no date fields). I added rowCnt for each client in the source database (SQL) where the number of rowCnt represents the week number starting from the earliest date for the client in the master table.

I tried to create a measure that calculates the average of the last 26 weeks for each client but that didn't work. Then i figured I can create a separate calculated table with the total averages per client but couldn't make that work either.

The SQL equivilent of what I'm looking for would be:

SELECT average(TotalCost) $$,

            average(ClaimsQuantity) Qty,

            database_name

FROM ...

WHERE rowCnt >= (max(rowCnt)-26)

GROUP BY database_name

Any help is appreciated!

Year	Week	TotalCost	ClaimsQuantity	memberswithclaims	rundate	database_name	rowCnt
2018	1	$790,229.93	7,334	879	11/18/2019	Client 1	105
2018	2	$760,093.71	3,589	833	11/18/2019	Client 1	106
2018	3	$274,556.92	7,201	290	11/18/2019	Client 1	107
2018	4	$ 80,066.32	6,201	535	11/18/2019	Client 1	108
2018	5	$515,801.28	7,299	620	11/18/2019	Client 1	109
2018	4	$621,371.65	1,554	273	11/18/2019	Client 2	6
2018	1	$382,295.08	9,957	289	11/18/2019	Client 3	20
2018	2	$271,687.48	1,561	374	11/18/2019	Client 3	21
2018	3	$720,261.23	8,254	629	11/18/2019	Client 3	22
2018	4	$211,872.72	5,660	631	11/18/2019	Client 3	23
2019	1	$ 34,853.87	4,923	113	11/18/2019	Client 1	158
2019	2	$   4,627.83	2,835	985	11/18/2019	Client 1	159
2019	3	$990,425.66	8,638	814	11/18/2019	Client 1	160
2019	4	$ 9,293.55	3,453	198	11/18/2019	Client 1	161
2019	5	$ 28,963.96	4,206	998	11/18/2019	Client 1	162
2019	1	$938,712.19	8,224	687	11/18/2019	Client 2	51
2019	2	$500,311.44	1,414	748	11/18/2019	Client 2	52
2019	3	$ 28,972.37	1,211	69	11/18/2019	Client 2	53
2019	4	$896,399.80	4,516	376	11/18/2019	Client 2	54
2019	5	$751,658.80	1,303	130	11/18/2019	Client 2	55
2019	1	$857,307.93	4,571	23	11/18/2019	Client 3	72
2019	2	$188,620.86	2,093	431	11/18/2019	Client 3	73
2019	3	$266,024.07	3,376	474	11/18/2019	Client 3	74
2019	4	$905,514.33	9,574	22	11/18/2019	Client 3	75
2019	5	$527,883.33	7,540	905	11/18/2019	Client 3	76

[Anonymous · Accepted solution]

Solved, hope this helps someone:

26-wk-avg Cost = AVERAGEX(
                    TOPN(26,
                        FILTER(
                               ALL(KeymetricsWeekly[rowCnt]),
                                KeymetricsWeekly[rowCnt] >= SELECTEDVALUE(MaxRowCntPerClient[Row - 26]) -- created separate table with max row count per client
                               ),
                         KeymetricsWeekly[rowCnt], DESC),
                         CALCULATE(AVERAGE(KeymetricsWeekly[TotalCost]))

[Greg_Deckler]

You might be able to adapt Sequential to give you a sequential week number per customer. 

https://community.powerbi.com/t5/Quick-Measures-Gallery/Sequential/m-p/380231?search-action-id=93121...

[Anonymous]

Hi Greg,

The cntRow field acts as a sequenctial per client where 1 = week 1 from the earliest year available.