Dynamic mean for control charts based on rules
Hello, I am trying to create a control chart based on a set of rules. My mean and standard deviation should change based on set of mentioned rules. Standard rules to calculate the control chart are as follows 1. to calculate mean/ average the minimum data points are 5. 2. To calculate standard deviation the minimum data points are 15. The required rules for the visualization 1. If seven consecutive points appear above or below the MEAN, the MEAN for the past seven points is recalculated. This is where we would look at the seven points ahead from the first point where it changed. 2. If seven consecutive points are moving either up or down the MEAN is recalculated on the past seven points. This again is where we would look at the seven points ahead from the first point where it changed. 3. If fifteen consecutive points are within +/- 1.0 standard deviation of the MEAN, the standard deviation, UCL and LCL are recalculated. Beginning to the system change point (back to the start of the 15 data points that triggered the recalc). Newly calculated means and standard deviations are used for rule evaluation on the next data points Sample data is as follows: Fiscal_Week Returns 5/7/2022 4630 5/14/2022 4800 5/21/2022 5069 5/28/2022 2789 6/4/2022 4596 6/11/2022 5275 6/18/2022 5172 6/25/2022 5219 7/2/2022 5101 7/9/2022 5020 7/16/2022 5327 7/23/2022 5001 7/30/2022 5221 8/6/2022 5309 8/13/2022 5225 8/20/2022 5475 8/27/2022 5291 9/3/2022 5371 9/10/2022 4961 9/17/2022 5091 9/24/2022 5355 10/1/2022 4049 10/8/2022 4729 10/15/2022 4869 10/22/2022 4417 10/29/2022 4030 11/5/2022 3698 Sample graph : Note: Not based on above mentioned data amitchandak Greg_Deckler Mikelytics mangaus1111
DAX formula to calculate rolling X average based on parameter & values from other columns
Hi all, I am trying to create a Column using DAX that calculates a rolling 8-week average based on a parameter/measure with multiple filters depending on the values of other columns. Data Structure: There is also a parameter for the current week number in cell K2: The column Forecast is what I am trying to create using DAX. What it does is: - If the week number is less than or equal to the Current Week, just copy the value under "Actual" (Column E) - Else, get the average of "Actual" (Column E) of the 8 weeks prior to the current week (if Current Week = 38, get average of weeks 30-37), subject to having the same FY, same KPI, and same Owner with the current row. In Excel, the correct formula for Row #2 would be: =IF( B2<=$K$2, E2, AVERAGEIFS( E:E, B:B,"<"&$K$2, B:B,">="&$K$2-8, D:D,D2, C:C,C2, A:A,A2 ) ) I am really struggling to find the right DAX syntax because I can't seem to figure out how to pass one of the values of the current row into the DAX filters. I tried doing something like this (without the IF statement) but obviously it's not working: I have uploaded the sample Excel and PBIX files for easier reference. You can see how the correct calculation is done in Excel. - XLSX: https://docs.google.com/spreadsheets/d/1fYG1dRtkthvLhxMTDXxbx1x_VmoVxf0Y/edit?usp=sharing&ouid=111734964497020988818&rtpof=true&sd=true - PBIX: https://drive.google.com/file/d/1TqT_xKZg3s7sSyZJr4yrQPtt-iW4mU8I/view?usp=sharing Any help will be greatly appreciated! Cheers, Andrew
Rolling Average showing future dates
HI all, I have a rolling average metric defined on top of a custom date table which should show only rolling 4 weeks (28 days): The metric is defined as: 1. Average of Open To Close rolling average 2 = IF( ISFILTERED('Date'[Week_Start_Date]), ERROR("Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy or primary date column."), VAR __LAST_DATE = LASTDATE('Date'[Week_Start_Date].[Date]) RETURN AVERAGEX( DATESBETWEEN( 'Date'[Week_Start_Date].[Date], DATEADD(__LAST_DATE, -28, DAY), __LAST_DATE ), CALCULATE(AVERAGE('ams_auto v_closed_ticket'[Open To Close])) ) ) The Date table is defined as: Date = ADDCOLUMNS ( CALENDAR (DATE(1990,1,1), DATE(2025,12,31)), "Date_ID", FORMAT ( [Date], "YYYYMMDD" ), "Week_Start_Date", DATEVALUE([Date] - WEEKDAY([Date],2) +1), "Year", YEAR ( [Date] ) ) So my question is, how can I limit the Rolling Average metric to not show dates in the future? Kind regards, Nerra
