Measure calculated for rolling specified period
Hello, i calculate Demand Plan Monthly Error as a measure DPME = |Demand Plan - Actual Sales| / Actual Sales The result looks similar like that: i also give informaction how DPME look for last 12 month altogether. In showed example is 77% (ist not average, DPME is calculated for last 12 mc altogether). I have to prepare a chart as above but instead of showing for yyyy-mm data for a given month, I have to show, data for the last 12 months. Example: For data prepared in Sep-24 in 2024.8 need to show DPME for period from 2024.8 to 2023.9 (for 12 months, but calculated from the month that was 1 months ago) in 2024.7 need to show DPME for period from 2024.7 to 2023.8 (for 12 months, but calculated from the month that was 2 months ago) in 2024.6 need to show DPME for period from 2024.6 to 2023.7 (for 12 months, but calculated from the month that was 3 months ago) (......) in 2023.9 need to show DPME for period from 2024.9 to 2022.10 (for 12 months, but calculated from the month that was 12 months ago) is it posiible to do some measures like that? which can be put on chart on the top?
12 Week Rolling Average without Dates
Good morning and Happy New Year, I have been wracking my brains for a while now on how to solve a problem. What I need to do is create a rolling 12 week average, which I seem to have done but the problem lies with the initial 12 weeks of the selected financial year. Rather then looking back at the 12 weeks in the previous financial year, its start off at week 1 and accumulates the average until it gets to week 12. Hopefully the visual below explains it better. Ideally, not sure if its possible in Power Bi to generate values in years that arent selected in the slicers, but I would want week 1 to be an average of the previous 12 weeks in the last financial year even if its not been selected so the user can select a year at a time rather then multiples. I have put the DAX below. _12W_RA = VAR CurrentWeek = MAX(DIM_CALENDAR[WEEK_OFFSET]) RETURN CALCULATE( SUMX( FILTER( ALLSELECTED(FCT_LCHID), FCT_LCHID[DIM_CALENDAR.WEEK_OFFSET] <= CurrentWeek && FCT_LCHID[DIM_CALENDAR.WEEK_OFFSET] >= CurrentWeek - 11 ), FCT_LCHID[LOST_CUSTOMER_HOURS] ) )/12 Any help would be great. Thanks RichRolling Totals for the last month without Date context
I am trying to get the 12 month Rolling Total Amount to show in a table where I dont have the date in the row context I have State or city dimention as row context I just need the rolling totals for the last month i.e. Nov 2021 to Oct 2022 I need the sum of the total amounts for the last 12 months as one of the columns in the table below (column 6) irrespective of whatever date filters / slicers are picked can someone please help with this
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!
creating a rolling measure on top of another rolling measure
I have a scenario where I am plugging numbers into an equation to calculate a safety rating. My client works in 13 periods in a year, 28 days (roughly) each in a year rather than months . So time intelligence functions wont work here. So the measure is supposed to calculate fatality rate over number of hours in a 28 day period. The trouble is due to data quality problems, ocassionally those hours dont come through, theyre blank. So to mitigate this we take an AVERAGE of the hours worked in the previous 6 periods and use that in calculation. Furthermore I am required to calculate a rolling average which might be based on this scenario. Here are my dax formulas: //This applies in scenario where there are no hours in the current period displaying TotalHoursAdjusted = Var currentPeriod = SELECTEDVALUE('PeriodLookup'[Period index]) return COALESCE(IF([TotalHours_KPIData] = 0,CALCULATE(DIVIDE([TotalHours_KPIData],6),filter(ALL('PeriodLookup'),'PeriodLookup'[Period index]>=(CurrentPeriod-6) && 'PeriodLookup'[Period index]<currentPeriod)),0),0) //Rolling 13 period hours RollingAdjustedhours = VAR CurrentPeriod = SELECTEDVALUE('PeriodLookup'[Period index]) VAR adjusted = [TotalHoursAdjusted] return COALESCE(CALCULATE([TotalHoursAdjusted],FILTER(ALL('PeriodLookup'),'PeriodLookup'[Period index]<=CurrentPeriod && 'PeriodLookup'[Period index]> (CurrentPeriod-13))),0) As you can see because they are both applying their filter contexts in table periodlookup (essentially my date table) the whole thing falls apart. I am not sure the best way to approach this. It seems like I need a temporary store for those adjusted hours somewhere. I tried to create a table variable but then I get an error telling me that the filter context cannot be applied between period lookup and my temporary table. It also seems like a computationally expensive way to calculate. Any ideas? Your input would be greatly appreciatedAdding 2 custom lines to line and stacked column chart
I currently have a pareto chart by number of cases per event date. I'm trying to add a rolling 7 day average calculation to this but I keep getting: Where I was hoping to have something like this? I'm currently running my 7 day rolling average by [Event Date]: 7 Day Rolling Average - Notification Date = CALCULATE(SUM('Sheet1'[Epi Date Count]),DATESINPERIOD('Sheet1'[Event Date],LASTDATE('Sheet1'[Event Date]),7,DAY)) / CALCULATE(DISTINCTCOUNT('Sheet1'[Event Date]),DATESINPERIOD('Sheet1'[Event Date],LASTDATE('Sheet1'[Event Date]),7,DAY)) Is there any way this can be done? Thanks in advance Please find file attached https://www.dropbox.com/s/c3hfci8jabydxv1/Pareto.pbix?dl=0
Rolling sum optimization
Hello, I populated a line chart with a cumulative function: Rolling count = SUMX(FILTER(ALLSELECTED(Tab[timestamp]),Tab[timestamp]<=MAX(Tab[timestamp])),CALCULATE(SUM(Tab[DeltaWithPreviousRowValue]),Tab[Item]="Console1", Tab[Sensor]="Counters/Cuts")) For each record I calculated the column [DeltaWithPreviousRowValue], I've got also the [value] which is a event incremental counter. I can't use time hiearchy on my axis because I need to prompt the line automatically in hours if few days are selected, and days if a long time range is selected in the date filter. Maybe a function that subtracts always the [Value] at start time and not using [DeltaWithPreviousRowValue] can work fine? In this case I have no idea how to fix pre-calculated MIN(Tab[value]) as a variable/sort of parameter. timestamp Item Sensor value Rank DeltaWithPreviousRowValue 11/06/2020 17:04:12 Console1 Counters/Cuts 2416 1003 1 11/06/2020 17:04:07 Console1 Counters/Cuts 2415 1002 1 11/06/2020 17:04:01 Console1 Counters/Cuts 2414 1001 1 11/06/2020 17:03:56 Console1 Counters/Cuts 2413 1000 1 11/06/2020 17:03:50 Console1 Counters/Cuts 2412 999 1 11/06/2020 17:03:45 Console1 Counters/Cuts 2411 998 1 11/06/2020 17:03:35 Console1 Counters/Cuts 2410 997 1 11/06/2020 17:03:14 Console1 Counters/Cuts 2409 996 1 11/06/2020 17:02:53 Console1 Counters/Cuts 2408 995 1 11/06/2020 17:02:35 Console1 Counters/Cuts 2407 994 1 11/06/2020 17:02:27 Console1 Counters/Cuts 2406 993 1 11/06/2020 17:02:22 Console1 Counters/Cuts 2405 992 1 11/06/2020 17:02:18 Console1 Counters/Cuts 2404 991 1 11/06/2020 17:02:15 Console1 Counters/Cuts 2403 990 2 11/06/2020 17:02:09 Console1 Counters/Cuts 2401 989 1 11/06/2020 17:02:05 Console1 Counters/Cuts 2400 988 2 11/06/2020 17:01:59 Console1 Counters/Cuts 2398 987 1 11/06/2020 17:01:56 Console1 Counters/Cuts 2397 986 2 11/06/2020 17:01:50 Console1 Counters/Cuts 2395 985 2 11/06/2020 17:01:44 Console1 Counters/Cuts 2393 984 1 11/06/2020 17:01:40 Console1 Counters/Cuts 2392 983 2 11/06/2020 17:01:34 Console1 Counters/Cuts 2390 982 1 11/06/2020 17:01:31 Console1 Counters/Cuts 2389 981 2 11/06/2020 17:01:25 Console1 Counters/Cuts 2387 980 2 11/06/2020 17:01:19 Console1 Counters/Cuts 2385 979 1 11/06/2020 17:01:15 Console1 Counters/Cuts 2384 978 2 11/06/2020 17:01:09 Console1 Counters/Cuts 2382 977 1 11/06/2020 17:01:06 Console1 Counters/Cuts 2381 976 2 11/06/2020 17:01:00 Console1 Counters/Cuts 2379 975 2 11/06/2020 17:00:54 Console1 Counters/Cuts 2377 974 1 Many thanks in advance!
