Attempt 2: Rolling 12 week measure

[MJ_BI]

Hi all,

I posted here 2 days ago – and despite numerous views on my original post – I have yet to get any feedback. I feel that this is my own fault as my original post may have been too verbose without providing any data for anyone to work with. I am now reattempting with an effort to keep things concise. I will also provide data for everyone this time in hopes it will help others to help me.

If you are interested, my original post can be read here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Rolling-12-WEEK-Measure/m-p/2171184#M50417

Goal:

I would like to compute a measure which can achieve a rolling 12-week ShippedCOGS total. If you are unfamiliar with COGS – for the sake of this post you can consider it as being equivalent to Sales(USD). Below is an example of what the output should produce:

Model:

The problem distills down to just two tables: Dim_Calendar and Fact_B2BSalesDiagnostic.  There is a 1:many relationship between the two tables with the 1-side tied to the Date field in Dim_Calendar and the many-side tied to the Week Start field in Fact_B2BSalesDiagnostic:

Data:

Since I am a new member, I do not yet have privilages to attach a PBIX file to this post.  As an alternative, here is the M code which produces the Dim_Calendar table being used:

***

EDIT: I've figured out I can use Google Drive to share the file here.  I'll link the url below, but will also keep the dummy data in the post in the event you are not comfortable downloading from strange links on the internet (trust me: I get it):

https://drive.google.com/file/d/1zbK_DO4HlIBDm9Xq64BqncDaq5HLj0kA/view?usp=sharing

***

let
    Source = #date(2021, 1, 1),
    Custom1 = List.Dates(Source, Number.From(DateTime.LocalNow()) - Number.From(Source), #duration(1, 0, 0, 0)),
    #"Converted to Table" = Table.FromList(Custom1, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Column1", type date}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Column1", "Date"}}),
    #"Inserted Year" = Table.AddColumn(#"Renamed Columns", "Year", each Date.Year([Date]), Int64.Type),
    #"Inserted Quarter" = Table.AddColumn(#"Inserted Year", "Quarter", each Date.QuarterOfYear([Date]), Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Inserted Quarter", "QuarterAbbr", each "Q" & Number.ToText([Quarter])),
    #"Inserted Month" = Table.AddColumn(#"Added Custom", "Month", each Date.Month([Date]), Int64.Type),
    #"Renamed Columns1" = Table.RenameColumns(#"Inserted Month",{{"Month", "MonthNumber"}}),
    #"Inserted Month Name" = Table.AddColumn(#"Renamed Columns1", "Month Name", each Date.MonthName([Date]), type text),
    #"Renamed Columns2" = Table.RenameColumns(#"Inserted Month Name",{{"Month Name", "Month"}}),
    #"Added Custom1" = Table.AddColumn(#"Renamed Columns2", "MonthAbbr", each Text.Start([Month], 3)),
    #"Inserted Week of Year" = Table.AddColumn(#"Added Custom1", "Week of Year", each Date.WeekOfYear([Date]), Int64.Type),
    #"Renamed Columns3" = Table.RenameColumns(#"Inserted Week of Year",{{"Week of Year", "WeekNumber"}}),
    #"Inserted Start of Week" = Table.AddColumn(#"Renamed Columns3", "Start of Week", each Date.StartOfWeek([Date]), type date),
    #"Inserted End of Week" = Table.AddColumn(#"Inserted Start of Week", "End of Week", each Date.EndOfWeek([Date]), type date),
    #"Extracted Text Before Delimiter" = Table.TransformColumns(#"Inserted End of Week", {{"Start of Week", each Text.BeforeDelimiter(Text.From(_, "en-US"), "/", 1), type text}}),
    #"Extracted Text Before Delimiter1" = Table.TransformColumns(#"Extracted Text Before Delimiter", {{"End of Week", each Text.BeforeDelimiter(Text.From(_, "en-US"), "/", 1), type text}}),
    #"Merged Columns" = Table.CombineColumns(#"Extracted Text Before Delimiter1",{"Start of Week", "End of Week"},Combiner.CombineTextByDelimiter("-", QuoteStyle.None),"Week Range"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Merged Columns",{{"QuarterAbbr", type text}, {"MonthAbbr", type text}}),
    #"Added Custom2" = Table.AddColumn(#"Changed Type1", "YrWkRngKey", each Number.ToText([Year]) &
"|" &
[Week Range]),
    #"Grouped Rows" = Table.Group(#"Added Custom2", {"YrWkRngKey"}, {{"All Rows", each _, type table [Date=nullable date, Year=number, Quarter=number, QuarterAbbr=nullable text, MonthNumber=number, Month=text, MonthAbbr=nullable text, WeekNumber=number, Week Range=text, YrWkRngKey=text]}}),
    #"Added Index" = Table.AddIndexColumn(#"Grouped Rows", "YrWkRngSort", 1, 1, Int64.Type),
    #"Expanded All Rows" = Table.ExpandTableColumn(#"Added Index", "All Rows", {"Date", "Year", "Quarter", "QuarterAbbr", "MonthNumber", "Month", "MonthAbbr", "WeekNumber", "Week Range", "YrWkRngKey"}, {"Date", "Year", "Quarter", "QuarterAbbr", "MonthNumber", "Month", "MonthAbbr", "WeekNumber", "Week Range", "YrWkRngKey.1"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Expanded All Rows",{"Date", "Year", "Quarter", "QuarterAbbr", "MonthNumber", "Month", "MonthAbbr", "WeekNumber", "YrWkRngKey", "YrWkRngSort", "Week Range", "YrWkRngKey.1"}),
    #"Removed Columns" = Table.RemoveColumns(#"Reordered Columns",{"YrWkRngKey.1"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Removed Columns",{{"WeekNumber", Int64.Type}, {"MonthNumber", Int64.Type}, {"Quarter", Int64.Type}, {"Year", Int64.Type}})
in
    #"Changed Type2"

Additionally, here is some dummy data for Fact_B2BSalesDiagnostic which was used to create the above PivotTable (+5 points if you get the movie references 😄 )

Week Start,Week End,ProductNumber,Product Title,Shipped COGS
6/13/2021,6/19/2021,1000,"Red Ryder, Carbine Action, 200 Shot Range Model Air Rifle",$28.91
6/13/2021,6/19/2021,1001,Bright Red Swingline Staple,$70.18
6/13/2021,6/19/2021,1002,Turbo-Man Action Figure,$54.50
6/20/2021,6/26/2021,1000,"Red Ryder, Carbine Action, 200 Shot Range Model Air Rifle",$29.68
6/20/2021,6/26/2021,1001,Bright Red Swingline Staple,$17.05
6/20/2021,6/26/2021,1002,Turbo-Man Action Figure,$50.91
6/27/2021,7/3/2021,1000,"Red Ryder, Carbine Action, 200 Shot Range Model Air Rifle",$55.37
6/27/2021,7/3/2021,1001,Bright Red Swingline Staple,$72.71
6/27/2021,7/3/2021,1002,Turbo-Man Action Figure,$59.15
7/4/2021,7/10/2021,1000,"Red Ryder, Carbine Action, 200 Shot Range Model Air Rifle",$62.45
7/4/2021,7/10/2021,1001,Bright Red Swingline Staple,$59.30
7/4/2021,7/10/2021,1002,Turbo-Man Action Figure,$61.59
7/11/2021,7/17/2021,1000,"Red Ryder, Carbine Action, 200 Shot Range Model Air Rifle",$28.71
7/11/2021,7/17/2021,1001,Bright Red Swingline Staple,$63.30
7/11/2021,7/17/2021,1002,Turbo-Man Action Figure,$66.53
7/18/2021,7/24/2021,1000,"Red Ryder, Carbine Action, 200 Shot Range Model Air Rifle",$41.40
7/18/2021,7/24/2021,1001,Bright Red Swingline Staple,$11.92
7/18/2021,7/24/2021,1002,Turbo-Man Action Figure,$30.24
7/25/2021,7/31/2021,1000,"Red Ryder, Carbine Action, 200 Shot Range Model Air Rifle",$54.48
7/25/2021,7/31/2021,1001,Bright Red Swingline Staple,$19.59
7/25/2021,7/31/2021,1002,Turbo-Man Action Figure,$17.03
8/1/2021,8/7/2021,1000,"Red Ryder, Carbine Action, 200 Shot Range Model Air Rifle",$57.65
8/1/2021,8/7/2021,1001,Bright Red Swingline Staple,$55.78
8/1/2021,8/7/2021,1002,Turbo-Man Action Figure,$31.21
8/8/2021,8/14/2021,1000,"Red Ryder, Carbine Action, 200 Shot Range Model Air Rifle",$70.92
8/8/2021,8/14/2021,1001,Bright Red Swingline Staple,$32.97
8/8/2021,8/14/2021,1002,Turbo-Man Action Figure,$33.68
8/15/2021,8/21/2021,1000,"Red Ryder, Carbine Action, 200 Shot Range Model Air Rifle",$47.05
8/15/2021,8/21/2021,1001,Bright Red Swingline Staple,$50.73
8/15/2021,8/21/2021,1002,Turbo-Man Action Figure,$62.98
8/22/2021,8/28/2021,1000,"Red Ryder, Carbine Action, 200 Shot Range Model Air Rifle",$11.72
8/22/2021,8/28/2021,1001,Bright Red Swingline Staple,$10.43
8/22/2021,8/28/2021,1002,Turbo-Man Action Figure,$26.21
8/29/2021,9/4/2021,1000,"Red Ryder, Carbine Action, 200 Shot Range Model Air Rifle",$70.95
8/29/2021,9/4/2021,1001,Bright Red Swingline Staple,$45.20
8/29/2021,9/4/2021,1002,Turbo-Man Action Figure,$21.10
9/5/2021,9/11/2021,1000,"Red Ryder, Carbine Action, 200 Shot Range Model Air Rifle",$77.49
9/5/2021,9/11/2021,1001,Bright Red Swingline Staple,$49.57
9/5/2021,9/11/2021,1002,Turbo-Man Action Figure,$56.97
9/12/2021,9/18/2021,1000,"Red Ryder, Carbine Action, 200 Shot Range Model Air Rifle",$64.70
9/12/2021,9/18/2021,1001,Bright Red Swingline Staple,$64.59
9/12/2021,9/18/2021,1002,Turbo-Man Action Figure,$72.10
9/19/2021,9/25/2021,1000,"Red Ryder, Carbine Action, 200 Shot Range Model Air Rifle",$73.70
9/19/2021,9/25/2021,1001,Bright Red Swingline Staple,$27.55
9/19/2021,9/25/2021,1002,Turbo-Man Action Figure,$37.89
9/26/2021,10/2/2021,1000,"Red Ryder, Carbine Action, 200 Shot Range Model Air Rifle",$78.75
9/26/2021,10/2/2021,1001,Bright Red Swingline Staple,$44.15
9/26/2021,10/2/2021,1002,Turbo-Man Action Figure,$12.24
10/3/2021,10/9/2021,1000,"Red Ryder, Carbine Action, 200 Shot Range Model Air Rifle",$18.73
10/3/2021,10/9/2021,1001,Bright Red Swingline Staple,$62.62
10/3/2021,10/9/2021,1002,Turbo-Man Action Figure,$30.32
10/10/2021,10/16/2021,1000,"Red Ryder, Carbine Action, 200 Shot Range Model Air Rifle",$70.31
10/10/2021,10/16/2021,1001,Bright Red Swingline Staple,$43.95
10/10/2021,10/16/2021,1002,Turbo-Man Action Figure,$22.86
10/17/2021,10/23/2021,1000,"Red Ryder, Carbine Action, 200 Shot Range Model Air Rifle",$25.19
10/17/2021,10/23/2021,1001,Bright Red Swingline Staple,$28.10
10/17/2021,10/23/2021,1002,Turbo-Man Action Figure,$67.63
10/24/2021,10/31/2021,1000,"Red Ryder, Carbine Action, 200 Shot Range Model Air Rifle",$77.03
10/24/2021,10/31/2021,1001,Bright Red Swingline Staple,$57.82
10/24/2021,10/31/2021,1002,Turbo-Man Action Figure,$35.04

What I've been able to do:

For the report I am building, I needed to compute the week-over-week percent change.  This obvisouly relies on knowing the prior week's shipped COGS total...which the following measure accomplishes this:

[ShippedCOGS - PriorPeriod] :=
CALCULATE (
    [B2BShippedCOGS],
    FILTER (
        ALL ( Dim_Calendar ),
        CONTAINS (
            VALUES ( Dim_Calendar[YrWkRngSort] ),
            Dim_Calendar[YrWkRngSort],
            Dim_Calendar[YrWkRngSort] + 1
        )
    )
)

As always, thanks everyone in advance for any help you may offer!  I hope we can crack this!

[AlexisOlson]

@Greg_Deckler's post should work for this except for nulling the period with less than 12-weeks of history.

Here's one way to insert that extra bit of logic:

Rolling 12-Week Shipped COGS =
VAR CurrWeekEnd = MAX ( Dim_Calendar[Date] )
VAR FirstWeekStart = CurrWeekEnd - 12 * 7 - 6
VAR FirstWeekCOGS =
    CALCULATE (
        SUM ( Fact_B2B[Shipped COGS] ),
        Dim_Calendar[Date] = FirstWeekStart
    )
RETURN
    IF (
        NOT ISBLANK ( FirstWeekCOGS ),
        CALCULATE (
            SUM ( Fact_B2B[Shipped COGS] ),
            DATESBETWEEN ( Dim_Calendar[Date], FirstWeekStart, CurrWeekEnd )
        )
    )

Without that extra complication, it's a bit simpler.

Rolling 12-Week Shipped COGS =
VAR CurrWeekEnd = MAX ( Dim_Calendar[Date] )
VAR FirstWeekStart = CurrWeekEnd - 12 * 7 - 6
RETURN
    CALCULATE (
        SUM ( Fact_B2B[Shipped COGS] ),
        DATESBETWEEN ( Dim_Calendar[Date], FirstWeekStart, CurrWeekEnd )
    )

[Greg_Deckler · Accepted solution]

@MJ_BI Are you looking for this? Rolling Weeks - Microsoft Power BI Community