Moving Average over weeks using a measure and a datedim

Hi Anonymous 

Assume you have data as below

Open edit queries, 

Add a column

let
    Source = Excel.Workbook(File.Contents("C:\Users\maggiel\Desktop\case\7\7.23\7.23.xlsx"), null, true),
    Sheet4_Sheet = Source{[Item="Sheet4",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Sheet4_Sheet, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"date", Int64.Type}, {"value", Int64.Type}}),
    #"Inserted Merged Column" = Table.AddColumn(#"Changed Type", "Merged_date", each Text.Combine({Text.Start(Text.From([date], "en-US"), 4), Text.Middle(Text.From([date], "en-US"), 4, 2), Text.Middle(Text.From([date], "en-US"), 6, 2)}, "/"), type text),
    #"Changed Type1" = Table.TransformColumnTypes(#"Inserted Merged Column",{{"Merged_date", type date}})
in
    #"Changed Type1"

Close&&Apply

Create new tables

calendar = ADDCOLUMNS(CALENDARAUTO(),"year",YEAR([Date]),"month",MONTH([Date]),"weeknum",WEEKNUM([Date],2))

weekset = VALUES('calendar'[weeknum])

Create measures

selected_weeknumber = SELECTEDVALUE(weekset[weeknum])

current_week = WEEKNUM(TODAY(),2)

last week = [current_week]-[selected_weeknumber]

clc_sum =
VAR clc_value =
    CALCULATE (
        SUM ( Sheet4[value] ),
        FILTER (
            ALLSELECTED ( Sheet4 ),
            Sheet4[Merged_date] <= MAX ( Sheet4[Merged_date] )
                && WEEKNUM ( Sheet4[Merged_date], 2 ) < [current_week]
                && WEEKNUM ( Sheet4[Merged_date], 2 ) >= [last week]
        )
    )
RETURN
    IF ( MAX ( 'calendar'[weeknum] ) = [current_week], BLANK (), clc_value )

clc_average = [clc_sum]/[selected_weeknumber]

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.