How to calculate Average of Running Total with Year and Week conditions

[Jouni900]

Hi,

I am currently following the running total of orders on a weekly level. In addition to this what I would like to see is an average weekly order using that running total. So far I haven't found a way on how to achieve the two objectives below  

1) I have order data on two consecutive years but I'd like to include only the current 2023 to the formula

2) If two weeks in a month have passed, I'd like the average weekly order to be calculated only using those two weeks' order data  

With the help of the "Filters on this visual" I have achieved so far 1) by restricting the Year to 2023 only. 

The measure that I have been able to produce is the following:

AVERAGEX( VALUES( 'Weekly Tracker calendar'[Week Start]), [Running Total] +0)

Is it possible to achieve both 1) and 2) with some modification of the current function or by creating it some way differently?

Thank you for the help!

[Jouni900]

Hi, @amitchandak 

Thanks for the reply, I am sharing the data and the formula that I used. I did not quite get the result I was looking for. Are you able to say what could cause this?

 Avg Weekly = calculate(AVERAGEX( VALUES( 'Weekly Tracker calendar'[Week Start]), [Running Total] +0), datesytd('Weekly Tracker calendar'[Date]) )

The formula produced a result of 62,63

Week Number	Running Total	Year
5	59,96	2022
6	130,9	2022
7	204,59	2022
8	291,76	2022
9	325,81	2022
5	29,79	2023
6	136,59	2023
7	225,66	2023
8	289,64	2023
9	304,55	2023

[amitchandak]

@Jouni900 , Try like

calculate(

AVERAGEX( VALUES( 'Weekly Tracker calendar'[Week Start]), [Running Total] +0), datesytd('Weekly Tracker calendar'[Date]) )

or

calculate(

AVERAGEX( VALUES( 'Weekly Tracker calendar'[Week Start]), [Running Total] +0), filter(all('Weekly Tracker calendar'), [Year] = max('Weekly Tracker calendar'[Year]) ) )