rolling months

5 Topics

Rolling Window Conditional Checks
I've got Bill and Melinda who've taken food safety classes: Person Food Safety Class Date Bill 3/18/2020 Melinda 4/15/2020 Once you've taken a food safety class you're qualified to perform food safety checks (FSC) for the next year. After that first 365 days is up you have to continuously perform at least 2 food safety checks for the previous 1 year period to maintain your food safety certification, and be able to perform more food safety checks. Here's the log of food safety checks: Person Food Safety Check FSC ID Bill 5/18/2020 FSC005 Bill 5/30/2020 FSC154 Bill 1/14/2021 FSC247 Bill 2/25/2023 FSC313 Melinda 6/15/2020 FSC005 Melinda 7/14/2020 FSC154 Melinda 2/28/2021 FSC247 Melinda 3/15/2021 FSC313 Melinda 3/29/2022 FSC399 Melinda 3/30/2022 FSC502 In Power BI DAX how can I determine which food safety checks were valid and which were not? The expected output would look like this: Person Food Safety Check FSC ID # FSC Past Year Validity Notes Bill 5/18/2020 FSC005 N/A Valid Grace period, as initial class was within the first year Bill 5/30/2020 FSC154 N/A Valid Grace period, as initial class was within the first year Bill 1/14/2021 FSC247 2 Valid None Bill 2/25/2023 FSC313 0 Invalid None Melinda 6/15/2020 FSC005 N/A Valid Grace period, as initial class was within the first year Melinda 7/14/2020 FSC154 N/A Valid Grace period, as initial class was within the first year Melinda 2/28/2021 FSC247 2 Valid None Melinda 3/15/2021 FSC313 2 Valid None Melinda 3/10/2022 FSC399 1 Invalid None Melinda 3/11/2022 FSC400 2 Invalid Once you're invalid everything after is invalid Melinda 3/12/2022 FSC401 3 Invalid Once you're invalid everything after is invalid To use Bill as an example: Bill passed his food safety class on 3/18/2020 After that Bill can perform food safety checks for 1 year, he performed two (FSC005, FSC154) and they are valid. Next Bill performed food safety check FSC247 and he did indeed perform at least 2 food safety checks in the previous 365 days, so this FSC247 is valid On 2/25/2023 Bill performed food safety check FSC313 and this is invalid because he had 0 food safety checks in the previous 365 days The last point in time Bill had at least 2 audits within the previous 365 days while still being qualified was 5/30/2021. Anything after 5/30/2021 is therefore invalid. I manually created and calculated the "# FSC Past Year" and "Validity" columns. How can I create them with DAX?
Solved
Anonymous
2 years ago Place DAX Commands and Tips
723Views
1like
1Comment
Help Needed - Rolling 12 Month Sales ignoring Year and Week Slicer
Hello Forum helpers Help needed in creating a Rolling 12 Month Sales ignoring Year and Week slicer. I've created a rolling 12 month for sales, but problem lies when using the slicer (Lets say I enter 2020), the Rolling sale calculation throwing wrong figure. Measures included in my PBIX file with Date table [Cumulative Rolling Sales, Month Running Index]. What I'm aiming for the 12 month Rolling measure which ignore Year and Week. Added a PBIX File to download with my example below. https://veetee0-my.sharepoint.com/:u:/g/personal/mnair_veetee_com/ET3qSWrPbG9GlpLfElsnuDcBQzKtd-TLXZ... Measure created based on solution provided by [TomMartens] https://community.powerbi.com/t5/Desktop/Rolling-Avg-calculation-should-ignore-date-slicer/td-p/6891.... Appreciate your help in advance. Many Thanks
Solved
Anonymous
2 years ago Place DAX Commands and Tips
836Views
0likes
1Comment
Remove the highs and low weeks of a rolling average across N weeks
Good afternoon everyone, I'm trying to identify high and low sales weeks in a summarized group of data in a rolling 6 weeks. Thus far I'm able to build the filtered 6 week period and summarize them as sales units by week. var filtered_table = FILTER(CONFORM_MOVEMENT, CONFORM_MOVEMENT[is_summary] <> 1 && CONFORM_MOVEMENT[date_key] > max(CONFORM_MOVEMENT[date_key]) - 42) var summarize_table = SUMMARIZE(filtered_table, 'Calendar'[Week],"sales_units_by_week", sum(CONFORM_MOVEMENT[SalesUnits])) return maxx(topn(1,summarize_table,[sales_units_by_week], desc),'Calendar'[Week]) When I attempt to return the maxx week of the rolling 6 weeks it brings over the current week rather than the top in the last 6 weeks. My expectation would be this on May 9th record looking back 6 weeks i would identify the high week to be April 11th and the low week to be april 4th. However, on Mar 28th i would see the high week as march 21st and the low week as february 21st. Any suggestions would be greatly appreciated! Thanks Channing
cworkman2015
5 years ago Place DAX Commands and Tips
489Views
0likes
1Comment
Rolling average with filter
Hi, I am trying to create 3months back rolling average with filter, becaue it needs to be rolling average per Plant. What I have created (or edited) below. *The table I'm working on was created with the Summarize function COGSTotal = SUMMARIZE(COGS,COGS[TotalByPlantKey],COGS[Plant],COGS[FixedDate],"Total COGS",SUM(COGS[ CostOfSale])) Total COGS FixedDate Plant 3monthsRollingAverage 2 01/01/2020 1 3 02/01/2020 1 2 03/01/2020 1 2.33333 5 04/01/2020 1 3.33333 4 01/01/2020 2 2 02/01/2020 2 3 03/01/2020 2 3 3 04/01/2020 2 4 *A circular error appears 3MonthsRollingAverage = VAR LastDate_ = LASTDATE ( COGSTotal[FixedDate]) RETURN CALCULATE ( AVERAGEX ( VALUES (COGSTotal[FixedDate]), CALCULATE ( SUM ( COGSTotal[Total COGS] ) ) ), FILTER ( COGSTotal, COGSTotal[Plant]=EARLIER(COGSTotal[Plant]) & [FixedDate] <= LastDate_ && [FixedDate] > DATEADD ( LastDate_, -3, MONTH ) ) )
Solved
arutsjak90
5 years ago Place DAX Commands and Tips
2.3KViews
0likes
9Comments
Rolling month
Hi , I am using below formula to count Calculated_required_dates by 12 month rolling TTM_sales3 = VAR Currentdate = EOMONTH(Max ('Date'[CLOSED_DATE]),0) var PreviousDate = EDATE ( EOMONTH (Currentdate, -1 ), -12 ) +1 VAR Result = CALCULATE (count('Work Delivery'[Calculated_REQUIRED_DATE]), Filter ('Work Delivery',MAX('Work Delivery'[CLOSED_DATE])>=PREVIOUSDATE && MAX('Work Delivery'[CLOSED_DATE])<= CURRENTDATE)) RETURN Result I am getting the exact result but total is missing.
Solved
Priya2007
6 years ago Place Report Server
971Views
0likes
1Comment