DAX - calculation of 3-month rolling average for a specific working day

Question

I have a table with a lot of dimensions, month, working day and amount. I would like to create a measure in PowerBI, which calculates the average of last 3 months for that particular working day. I can create similar in SQL using AVG OVER (PARTION BY) but I would like to have it as a dynamic measure which changes when user filters on the different dimensions.

example table, if I see total work day 5 I should get average of 49.3 (= sum of 148 / 3 months)

Many thanks and let me know if any further information is needed.

I am a beginner in DAX and I could not find a similar problem online. I can solve it in SQL but then I need a dynamic measure in PBI.

Edit: Hi, Sam Nseir, thank you for the help. In first solution, if I remove the divide by 3 I get the same numbers as current work day. Same in the second solution.

Below is the result, when I filter on work day 8 in my report. enter image description here

Answer 1

You can create Measure similar to this one:

RAvg 3-Month = 
  AVERAGEX(
    DISTINCT('YourTable'[Working Day]),
    CALCULATE(
      AVERAGEX(
        DISTINCT('YourTable'[Month]),
        CALCULATE(SUM('YourTable'[Amount]))
      ),
      DATESINPERIOD('YourTable'[Month], MAX('YourTable'[Month]), -3, MONTH)
    )
  )

Result:

Answer 2

What if you return return m3amt instead of DIVIDE(m3amt, 3)?

RAvg 3-Month = 
  AVERAGEX(
    DISTINCT('YourTable'[WorkingDay]),
    (
      var m3amt = 
        CALCULATE(
          SUM('YourTable'[Amount]),
          DATESINPERIOD('YourTable'[Month], MAX('YourTable'[Month]), -2, MONTH)
        )
      return m3amt
    )
  )