powerbidaxrolling-average

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

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

Solution

  • 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:
    enter image description here