Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
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 |
@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]) ) )
User | Count |
---|---|
128 | |
108 | |
100 | |
65 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |