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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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.

 

QuestionOnMovingAverage.PNG

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!
 
 
 
 
 
 
1 ACCEPTED SOLUTION
v-juanli-msft
Community Support
Community Support

Hi @Anonymous 

Assume you have data as below

date value
2019070103 1
2019070203 2
2019070303 3

Open edit queries, 

Add a column

8.png

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

9.png

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]

7.png

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.

View solution in original post

3 REPLIES 3
v-juanli-msft
Community Support
Community Support

Hi @Anonymous 

Assume you have data as below

date value
2019070103 1
2019070203 2
2019070303 3

Open edit queries, 

Add a column

8.png

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

9.png

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]

7.png

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.

v-juanli-msft
Community Support
Community Support

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
Not applicable

Hi @v-juanli-msft 

 

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

Here're some of the relevant columns.

 

DateDim example.PNGResults example.PNG

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.