advanced Moving average of last 10 days

[Fish83]

Spoiler

Spoiler

Hi all,

I am trying to calculate the moving average of the last 10 working days. I have data about order processing with columns: order processing date, number of processed orders. given is also a related date table with columns: date, IsWorkingDay, IsHoliday. 
All the dax calculation I tried sofar did not give me the right number. this is because there are working days where there is no processing, and processing is also done occassionally on holidays and weekends. the data is giving in the below table. the 10th of march is a holiday in switzerland and the 8th & 15th of march are weekends. on the 14th of march (friday), there were no processing though working day. 

Thank you in Advance for your help.

Date	Orders Volume
05.03.2025	21
06.03.2025	16
07.03.2025	2
07.03.2025	1
08.03.2025	63
10.03.2025	23
11.03.2025	70
12.03.2025	27
12.03.2025	3
13.03.2025	34
15.03.2025	25
17.03.2025	1
17.03.2025	20
18.03.2025	37
19.03.2025	2

 the date table is below:

Date           Year  M     Month  W     WD   Holiday

05.03.25	2025	3	März	10	True	False
06.03.25	2025	3	März	10	True	False
07.03.25	2025	3	März	10	True	False
08.03.25	2025	3	März	10	False	True
09.03.25	2025	3	März	10	False	True
10.03.25	2025	3	März	11	True	True
11.03.25	2025	3	März	11	True	False
12.03.25	2025	3	März	11	True	False
13.03.25	2025	3	März	11	True	False
14.03.25	2025	3	März	11	True	False
15.03.25	2025	3	März	11	False	True
16.03.25	2025	3	März	11	False	True
17.03.25	2025	3	März	12	True	False
18.03.25	2025	3	März	12	True	False
19.03.25	2025	3	März	12	True	False

[v-veshwara-msft]

Hi @Fish83 ,

Thanks for reaching out to Microsoft Fabric Community.

Just checking in to see if your issue is resolved by now. If not, please consider the following:

To correctly calculate the moving average for the last 10 working days, consider using a fixed anchor date such as a date slicer selection or a separate reference date in your model rather than the latest order processing date, which may have gaps. This approach ensures consistency even when there are missing processing dates in your data.

For identifying working days, make sure your logic includes both IsWorkingDay = True() and IsHoliday = False() conditions together, so holidays are excluded properly when calculating the list of last 10 working days.

Hope this helps.

If you have found another solution that works for you, please share it here to help the community.

If any response in this thread was helpful, please mark it as the accepted solution to assist others with similar queries. A kudos would also be appreciated.

Thank you.

[sanalytics]

@Fish83  One request for you..Can you provide your expected output result ,it will help us to write the code for your measure.

Thanks

sanalytics

[Fish83]

Hi Sanalytics,

Thank you for your help. Sure, the outcome should be about 34.

[Fish83]

Hi Bhanu,

Thank you for your Solution. Unfortunately it's still not working. first, the IsWorkingDay needs also to consider Holidays (Is Holiday = False). Also, I can not take the VAR current Date from the processing date, as I have missing dates (that are working days, but no processing was done). Also getting the below error:

"The number of arguments is invalid. Function CONTAINSROW must have a value for each column in the table expression"

Thank you again

[bhanu_gautam]

@Fish83 Create a calculated column to identify the working days in your order processing table:

IsWorkingDay =
LOOKUPVALUE(
'DateTable'[WD],
'DateTable'[Date],
'OrderProcessing'[Order Processing Date]
)

Create a measure to calculate the moving average of the last 10 working days:

Moving Average Last 10 Working Days =
VAR CurrentDate = MAX('OrderProcessing'[Order Processing Date])
VAR Last10WorkingDays =
CALCULATETABLE(
TOPN(
10,
FILTER(
ALL('DateTable'),
'DateTable'[Date] <= CurrentDate && 'DateTable'[WD] = TRUE()
),
'DateTable'[Date],
DESC
)
)
RETURN
AVERAGEX(
FILTER(
'OrderProcessing',
'OrderProcessing'[Order Processing Date] IN Last10WorkingDays
),
'OrderProcessing'[Orders Volume]
)