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
Jouni900
Frequent Visitor

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

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!
2 REPLIES 2
Jouni900
Frequent Visitor

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 NumberRunning TotalYear
559,962022
6130,92022
7204,592022
8291,762022
9325,812022
529,792023
6136,592023
7225,662023
8289,642023
9304,552023
amitchandak
Super User
Super User

@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]) ) )

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.