Long-term average calculations by month based on selected month in a slicer

[Sambath]

Long-term average calculations based on selected month in a slicer

I have a time series dataset from Feb 2012 to May 2023 and wanted to create a simple visual to compare how number of orders in the last 12 months compare to long-term averages, as below:

• bars represent orders within the last 12 months to month selected in a slicer, Nov 2022 for this example, hence the graph cover Dec 2021-Nov 2022
• black line represents month average, excluding last 12 months in the visual, i.e. orders in Dec 2021 – current month are excluded from the average calculation
• grey lines represent monthly average+/- one standard deviation

To achieve last 12 month visual, I created two date tables: ‘Calendar 1’ and ‘Calendar 2’ with one to many inactive relationship and used ‘Calendar 2’[Month Year]; eg. Nov 2022; as a slicer.

To calculate average, I came across various approaches using either VALUES, SUMMARIZE, DATESINPERIOD or others and this DAX seems to work if my visual is based on [Month name] but it is not working for me as my visual X-axis is based on [Month Year].

Avg using SUMMARIZE =

AVERAGEX(

SUMMARIZE( 'Calendar','Calendar'[Month Year],

"_Avg", [Total Number of Orders]), [_Avg])

Any advise on how to (i) dynamically filter to Feb 2012 to the selected month minus 12 months, eg. Feb 2012 to Sep 2021 and (ii) work out the average for each month? Average for Nov should be sum of order numbers in all 11 Novembers divided by 11 months, …, and sum of order numbers in all 10 Januaries divided by 10 and same for December.  

Notes: my data has some months where there are no orders and I replaced the blanks with 0 using below DAX:

[Total Number of Orders] = IF (ISBLANK( DISTINCTCOUNT(‘Orders’[Order ID])), 0 ,

DISTINCTCOUNT(‘Orders’[Order ID]))

Thanks in advance for any assistance or guidance.