Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
cho
Helper I
Helper I

Rolling average in Power Query

Hi Experts!

I have a table (after few steps to the original table) with below columns (sample):

CurrencyYearMonthSales
USD2019121.1234
CZK20191225.408
GBP2019120.8508
RON2019124.783
CNY2019127.8205
EUR2019121
USD2020011.1052
CZK20200125.21
GBP2020010.84175
RON2020014.7789
CNY2020017.6664
EUR2020011
USD2020021.0977
CZK20200225.39
GBP2020020.85315
RON2020024.813
CNY2020027.6662
EUR2020021
USD2020031.0956
CZK20200327.312
GBP2020030.88643
RON2020034.8283
CNY2020037.7784
EUR2020031

 

I need a custom column with below condition:
From YearMonth column, pick the last months' Sale, starting from January of that particular year to that particular month, including last year's December value, and take the average of those Sales according to that Currency.

 

I added a custom column with a code like this:

 

let
    currency = [Currency],
    year = Number.FromText(Text.Start(Text.From([YearMonth]), 4)),
    month = Number.FromText(Text.End(Text.From([YearMonth]), 2)),
    months_to_include = List.Numbers(
        if month < 7 then 12 - month + 1 else 1,
        if month < 7 then 6 else month,
        1
    ),
    last_year_rows = Table.SelectRows(
        #"Removed Other Columns",
        each [Currency] = currency and
             Number.FromText(Text.Start(Text.From([YearMonth]), 4)) = year - 1 and
             Number.FromText(Text.End(Text.From([YearMonth]), 2)) > 6 - month and
             Number.FromText(Text.End(Text.From([YearMonth]), 2)) <= 12
    ),
    current_year_rows = Table.SelectRows(
        #"Removed Other Columns",
        each [CURRENCY_ID] = currency and
             Number.FromText(Text.Start(Text.From([YearMonth]), 4)) = year and
             Number.FromText(Text.End(Text.From([YearMonth]), 2)) <= month
    ),
    all_rows = current_year_rows & last_year_rows,
    filtered_rows = Table.SelectRows(
        all_rows,
        each [YearMonth] >= Number.ToText(year * 100 + months_to_include{0}, "0000") and [YearMonth] <= [YearMonth]
    ),
    avg_Sales = List.Average(filtered_rows[Sales])
in
    avg_Sales

 

 

But now, I although values are correct after July, from January to June values are NULL. Can someone please help me to fix this?
I understand the error should be in "months_to_include", but I do not know how to fix it. Thank you!

1 ACCEPTED SOLUTION
ppm1
Solution Sage
Solution Sage

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

 

Pat

Microsoft Employee

View solution in original post

3 REPLIES 3
ppm1
Solution Sage
Solution Sage

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

 

Pat

Microsoft Employee

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
    )
)

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

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors