Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Rolling average in Power Query

Hi Experts! I have a table (after few steps to the original table) with below columns (sample): Currency YearMonth Sales USD 201912 1.1234 CZK 201912 25.408 GBP 201912 0.8508 ...
Need Help
  • ppm1's avatar
    ppm1
    3 years ago

    This type of calculation is much easier with DAX (and a Date column vs. date integer). Have you already tried that?

     

    Pat

ppm1's avatar
ppm1
Solution Sage
3 years ago

This type of calculation is much easier with DAX (and a Date column vs. date integer). Have you already tried that?

 

Pat

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Yes, I added a calculated column like this but it gives wrong values. 

    Average= 
VAR currentYearMonth = [YearMonth]
VAR currentMonth = RIGHT(currentYearMonth, 2)
VAR currentYear = LEFT(currentYearMonth, 4)
VAR lastYearMonth = IF(currentMonth = "01", FORMAT(VALUE(currentYear) - 1, "0000") & "12", FORMAT(VALUE(currentYear), "0000") & FORMAT(VALUE(currentMonth) - 1, "00"))
VAR numMonths = VALUE(currentMonth) + (12 * (VALUE(currentYear) - VALUE(LEFT(lastYearMonth, 4))))
RETURN
CALCULATE(
    AVERAGE('table'[Sales]),
    FILTER(
        'table',
        'table'[Currency] = EARLIER('table'[currency]) &&
        'table'[YearMonth] >= lastYearMonth &&
        'table'[YearMonth] <= currentYearMonth
    )
)
    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Since there are other calculations those cannot be done in desktop, I need to do this in Power Query 😞