Average running totals per row

[jamc]

Hi 

I am trying to find the following average Running result using for the following exercise:

Week	SALES	AVERAGE FORMULA LOGIC	Desirable  Result
W1	100	WK1 to WK13	238
W2	150	WK2 to WK13	250
W3	200	WK3 to WK13	259
W4	250	WK4 to WK13	265
W5	300	WK5 to WK13	267
W6	350	WK6 to WK13	263
W7	400	WK7 to WK13	250
W8	350	WK8 to WK13	225
W9	300	WK9 to WK13	200
W10	250	WK10 to WK13	175
W11	200	WK11 to WK13	150
W12	150	WK12 to WK13	125
W13	100	WK13 to WK13	100

Any help would be appreciate it 

[jamc]

Hi community i am still looking for the correct Dax Formula for the following excercise, is the only one left me to do  I have bieng different rolling average without  success, ill attach the logic of the excersice in green is the elements that i have in my model, in blue is the diserable result that i need populated in the same column, and in yelow the logic of the calculation please is the last thing I need to complete my project, I ll really apreciate it

Example

[speedramps]

Hi Jamc

Click here to download an example solution 

Add a index column because W1, W11, W12, !3 will sort before W2.

Then add DAX measure ... 

Result =

VAR CurrentWeek = SELECTEDVALUE(Facts[Index])   // gets the row week

RETURN
// get the average for all weeks >= the current week

CALCULATE(

AVERAGE(Facts[Sales]),                                

ALL(),

Facts[Index] >= CurrentWeek

)

I am an unpaid Power BI volunteer. Please click the thumbs up if you like me trying to help. Then click solved if this fixes your problem. Thank you.

Warm regards, speedramps

[mahoneypat]

Once you convert the Week column to integer (for example, with Text After Delimiter "W"), you can use a DAX measure like this one.

WkAvgSales =
VAR vThisWeek =
    MIN( WeeklySales[Week] )
VAR vResult =
    CALCULATE( AVERAGE( WeeklySales[SALES] ), WeeklySales[Week] >= vThisWeek )
RETURN
    vResult

Pat

[jamc]

Hi it was very Helpfull

But now I was asked for more compexity, I need to organize it by product and by date

Item	WEEK	Sales	Desirrable result
Product A	4/18/2022 0:00	10	40
Product A	4/25/2022 0:00	15	42.5
Product A	5/2/2022 0:00	20	45
Product A	5/9/2022 0:00	25	47.5
Product A	5/16/2022 0:00	30	50
Product A	5/23/2022 0:00	35	52.5
Product A	5/30/2022 0:00	40	55
Product A	6/6/2022 0:00	45	57.5
Product A	6/13/2022 0:00	50	60
Product A	6/20/2022 0:00	55	62.5
Product A	6/27/2022 0:00	60	65
Product A	7/4/2022 0:00	65	67.5
Product A	7/11/2022 0:00	70	70
Product B	4/18/2022 0:00	65	35
Product B	4/25/2022 0:00	60	32.5
Product B	5/2/2022 0:00	55	30
Product B	5/9/2022 0:00	50	27.5
Product B	5/16/2022 0:00	45	25
Product B	5/23/2022 0:00	40	22.5
Product B	5/30/2022 0:00	35	20
Product B	6/6/2022 0:00	30	17.5
Product B	6/13/2022 0:00	25	15
Product B	6/20/2022 0:00	20	12.5
Product B	6/27/2022 0:00	15	10
Product B	7/4/2022 0:00	10	7.5
Product B	7/11/2022 0:00	5	5

I was trying withs this formula but it didnt return the result that I wanted:

RR = VAR RRWEEK =

MIN('TRIAL VERSION'[Week] ) VAR RESULT = CALCULATE(AVERAGE('TRIAL VERSION'[SALES]), FILTER(ALLSELECTED('TRIAL VERSION'), 'TRIAL VERSION'[PRODUCT] = MIN('TRIAL VERSION'[PRODUCT] ) && 'TRIAL VERSION'[Week]<=RRWEEK)) RETURN RESULT

Thanks

[Ashish_Mathur]

Please explain how you got the result in the desired result column.

[speedramps]

Ashish

It looks like Jamc has manually calulated the desired column,

by getting the average for

WK11 to WK13
WK12 to WK13
WK13 to WK13

etc  to etc

Speedramps and Mahoneypat both provided viable solutions to get the result using PBI.

[jamc]

Hi ASHISH

I leave the logic of the average calculation in an add last column

Item	WEEK	Sales	Desirrable result	AVERAGE FORMULA LOGIC
Product A	4/18/2022	10	40	FROM Product A  WK1 TO WK 13
Product A	4/25/2022	15	42.5	FROM Product A  WK2 TO WK 13
Product A	5/2/2022	20	45	FROM Product A  WK3 TO WK 13
Product A	5/9/2022	25	47.5	FROM Product A  WK4 TO WK 13
Product A	5/16/2022	30	50	FROM Product A  WK5 TO WK 13
Product A	5/23/2022	35	52.5	FROM Product A  WK6 TO WK 13
Product A	5/30/2022	40	55	FROM Product A  WK7 TO WK 13
Product A	6/6/2022	45	57.5	FROM Product A  WK8 TO WK 13
Product A	6/13/2022	50	60	FROM Product A  WK9 TO WK 13
Product A	6/20/2022	55	62.5	FROM Product A  WK10 TO WK 13
Product A	6/27/2022	60	65	FROM Product A  WK11 TO WK 13
Product A	7/4/2022	65	67.5	FROM Product A  WK12 TO WK 13
Product A	7/11/2022	70	70	FROM Product A  WK13 TO WK 13
Product B	4/18/2022	65	35	FROM Product B  WK1 TO WK 13
Product B	4/25/2022	60	32.5	FROM Product B  WK2 TO WK 13
Product B	5/2/2022	55	30	FROM Product B  WK3 TO WK 13
Product B	5/9/2022	50	27.5	FROM Product B  WK4 TO WK 13
Product B	5/16/2022	45	25	FROM Product B  WK5 TO WK 13
Product B	5/23/2022	40	22.5	FROM Product B  WK6 TO WK 13
Product B	5/30/2022	35	20	FROM Product B  WK7 TO WK 13
Product B	6/6/2022	30	17.5	FROM Product B  WK8 TO WK 13
Product B	6/13/2022	25	15	FROM Product B  WK9 TO WK 13
Product B	6/20/2022	20	12.5	FROM Product B  WK10 TO WK 13
Product B	6/27/2022	15	10	FROM Product B  WK11 TO WK 13
Product B	7/4/2022	10	7.5	FROM Product B  WK12 TO WK 13
Product B	7/11/2022	5	5	FROM Product B  WK13 TO WK 13

hope you can help

regards

[Ashish_Mathur]

Hi,

I can help with solving this question is you can create a Calendar Table and in that table also have a week number column.  So please share the download link of your PBI file with your Data Table and the Calendar Table.