Rolling 12 WEEK Measure
Hi all, The funny thing about DAX is that as soon as I feel like I'm getting the hang of things...a new problem pops up which brings me back to earth. In my current dilemma, I cannot seem to come up with a way to compute a rolling 12 week total for shipped COGS (cost of goods sold). Here's a bit of background: In my model there is a fact table named Fact_B2BSalesDiagnostic comprised of several Excel files appended together using PowerQuery. Each Excel file is a table which reports sales figures for multiple products across a single week in time...where each row represents a specific product's performance in the given week. You can imagine a row in one of the original Excel files as resembling something like: (ProductNumber, ProductName, UnitsShipped, ShippedCOGS) While connecting PowerQuery to the folder containing the Excel files, the Week Start and Week End columns were appended to each row. In this way, we can define a relationship between the Date field of Dim_Calendar and either Week Start or Week End of Fact_B2BSalesDiagnostic in order to define measures which can achieve weekly totals. The image below depicts the relationship I have used: Additionally, I have defined YrWkRngKey and YrWkRngSort fields in Dim_Calendar. The best way to describe these fields is through example: Assume we are looking at a row in Dim_Calendar with a Date of 28-Sep-2021 The week range (sunday to sunday) this date belongs to is 26-Sep-2021 thru 2-Oct-2021 As such, the YrWkRngKey reads: "2021|9/26-10/2" (Note each row with date between 26Sep21 and 2Oct21 will have this same exact value) The YrWkRngSort field is simply an index column which numbers the unique values of YrWkRngKey Having defined things as such, I was able to create a measure which computes ShippedCOGS for the prior period as follows: [B2BShippedCOGS - PriorPeriod] := CALCULATE ( [B2BShippedCOGS], FILTER ( ALL ( Dim_Calendar ), CONTAINS ( VALUES ( Dim_Calendar[YrWkRngSort] ), Dim_Calendar[YrWkRngSort], Dim_Calendar[YrWkRngSort] + 1 ) ) ) Where: [B2BShippedCOGS] := SUM( Fact_B2BSalesDiagnostic[Shipped COGS] ) However -- I can't seem to find a way to get a rolling 12 week total for shipped COGS! I've been reading articles and trying things for 2 hours now without any luck, and I'm turning to you folks in the hopes that your expertise can help me get through this! Thanks in advance for any help you may offer. Hope you have a good one!Attempt 2: Rolling 12 week measure
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!
Calculate Change from Previous Week Not Working
Hello All, I have a small issue with my report. What i am trying to show is the change in headcount from previous week. i found this dax measure and it works when a user selects a specific date: Change from Previous WK = VAR lastWeekAmount = CALCULATE ( DISTINCTCOUNT( SNAP_DWH_RES_RESOURCE[resource_key] ), FILTER ( ALL ( DATE_DIMENSION ), DATE_DIMENSION[Week of Year] = MIN ( DATE_DIMENSION[Week of Year] ) - 1 ) ) RETURN DISTINCTCOUNT( SNAP_DWH_RES_RESOURCE[resource_key] ) - lastWeekAmount So if the user selects December 1 from the line chart the Headcount in the table changes to reflect correctly and the Change from Previous Week is calculated correctly... previous week was 763 However, when no specific date is selected the table correctly shows the latest headcount of 767 but DOES NOT show the change from previous week which was 3 (if the user selects December 8 specifically it does work). I presume its because the measure cannot calculate as a specific date is not in focus... but i cant seem to overide... So, how do i modify the measure (or even create another measure) which will correctly show latest headcount and change from previous week when a specific date isnt selected? Thanks, Andy
Not able to get totals in Power BI
I want to understand, why I am not getting totals in some senarios in the table. Why does this happen and what I must do to avoid this? The measures I used are: 1. 0_Last week Cost of Freight = VAR CurrentWeek = SELECTEDVALUE('dDate'[Week No.]) VAR CurrentYear = SELECTEDVALUE('dDate'[Year]) VAR MaxWeekNumber = CALCULATE(MAX('dDate'[Week No.]),ALL('dDate')) RETURN CALCULATE([Cost of Freight],FILTER(ALL('dDate'),IF(CurrentWeek=1, 'dDate'[Week No.] = MaxWeekNumber && 'dDate'[Year] = CurrentYear-1, 'dDate'[Week No.] = CurrentWeek-1 && 'dDate'[Year] = CurrentYear)) ) 2. Cost of Freight = SUM('fKPI 2019'[Load Charge]) 3. 0_Weekly change of cost = [Cost of Freight]-[0_Last week Cost of Freight]
