Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Moving Average over weeks using a measure and a datedim

Hi! I'm designing a visualization on which I'd like to show a 4 week over of a measure.

 

The moving average will be based on the 'Berth Productivity' line which uses the following measure:

DS_BP = CALCULATE(SUM('Results'[WeightedBP]) / SUM('Results'[TotalMoves]);FILTER('Results'; 'Results'[Type] = "Ship"))
 
My 4week average measure, however. Should base his information using the datedim. The link bebetween datedim and the 'Results' table, is a date_hour_key (for example 2019071605).
 
The dashboard is a weekly report, reporting data of the last completed week. To filter out the right weeks, the datedim has a moving column that indicates the current week as 0, previous week as -1, week before as -2, etc.
 
Hope you guys can help!
 
 
 
 
 
 
Need Help
  • v-juanli-msft's avatar
    v-juanli-msft
    7 years ago

    Hi Anonymous 

    Assume you have data as below

    date value
    2019070103 1
    2019070203 2
    2019070303 3

    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.

3 Replies

  • v-juanli-msft's avatar
    v-juanli-msft
    Icon for Community Support rankCommunity Support
    7 years ago

    Hi Anonymous 

    Could you show an example of datedim and 'Results' table?

    Can i connect them via "date"(eg.20190716) field?

     

    Here is a useful link you could refer to

    Moving Averages Controlled by Slicer

     

    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.

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Hi v-juanli-msft 

       

      You can indeed use that key to link to the datedim.

      Here're some of the relevant columns.

       

  • v-juanli-msft's avatar
    v-juanli-msft
    Icon for Community Support rankCommunity Support
    7 years ago

    Hi Anonymous 

    Assume you have data as below

    date value
    2019070103 1
    2019070203 2
    2019070303 3

    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.