Moving average weekly
Hello, I have calculated measure (values) and I want to see moving average based on weeks. This is what it should look like: Week values moving average - should be 1 187 750 187 750 2 493 333 340 541,5 3 775 840 485 641 I tried this formula but it shows wrong values: Moving average = averagex( FILTER(ALLSELECTED(Kalender), Kalender[Datum] <= MAX(Kalender[Datum)), [Gesamtleistung]) Do you have any ideas?
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
Getting previous 3MMA
Hi, can someone help me to figure out how to get this? currently i have 3MMA but how to get Jan 2022 and Nov, Dec 2021 measure for 3mma 3MMA SOM = CALCULATE(AVERAGEX(values(mv_fact_sales_aggr[MonthYear]),[SOM]),DATESINPERIOD(mv_fact_sales_aggr[date],LASTDATE(mv_fact_sales_aggr[date]),-3,MONTH)) Thanks.Inserting Moving average to Visuals with Month yearly trend
hi having problem with this visual any suggestion how to insert the filter of mma 3mma 12mma in my visual is montly share of market by company measure: SOM = divide(sum(mv_fact_sales_aggr[Wfactor Sales]),CALCULATE(sum(mv_fact_sales_aggr[Wfactor Sales]),ALLSELECTED(mv_fact_sales_aggr[company],mv_fact_sales_aggr[brand_family_name],mv_fact_sales_aggr[brand_name],mv_fact_sales_aggr[product_name],mv_fact_sales_aggr[volpricetier],mv_fact_sales_aggr[regional],mv_fact_sales_aggr[zone],mv_fact_sales_aggr[area],mv_fact_sales_aggr[Year],mv_fact_sales_aggr[price_segment],mv_fact_sales_aggr[Weighting Factors],mv_fact_sales_aggr[segment],mv_fact_sales_aggr[product_classifier]),ALL(mv_fact_sales_aggr[brand_family_name])),0)*100 and im using switch for my filter measure for 3mma : 3MMA SOM = CALCULATE(AVERAGEX(values(mv_fact_sales_aggr[MonthYear]),[SOM]),DATESINPERIOD(mv_fact_sales_aggr[date],LASTDATE(mv_fact_sales_aggr[date]),-3,MONTH)) Is it possible to filter it by Moving average with a monthly chart?
Running Average Forecast
Hello all, i want to create a forecast based on past values in PowerBI using a measure. I have a big set of data with various information for the previous months (January to June) and need to use this data to create a forecast for the upcoming months with an average function. i.e. July vale = average of April may June white values are available, yellow data is not available and needs to be calculated. So it's kind of a Loop, I need to use the calculated average in October to calculate the average of November...
Measure for rolling average by fiscal quarter with filters
I am trying to create a rolling average by fiscal quarter in Power BI and apply filters to a line graph to select only data for a certain date range and display only data for a certain date range. For confidentiality reasons I can’t share the real dataset, so I created a fake dataset that is similar. The dataset is market research survey data and my fake dataset contains three columns: Date, Q1 which is a question asked on a scale from 0-10, and SEGMENT which is either “Segment 1” and “Segment 2”. In my fake dataset I generated random values for Q1 and SEGMENT. The client I am working for uses a fiscal year of November 1-October 31 (so November 1, 2019-January 31, 2020 is Fiscal Q1 2020) and in the real dataset no data was collected for April 1, 2020-July 31, 2020 (Fiscal Q3 2020) due to COVID-19; survey data was collected for all fiscal quarters before and after Fiscal Q3 2020. The issue is that Rolling Q3 2020 is not showing up on my line graph; even though there is no data for Rolling Q3 2020 that data point should still display on the graphs, using the average of all data from Q4 2019, Q1 2020 and Q2 2020. My main dataset is Sheet1 imported from the Excel file Rolling_Quarter_Fake_Data.xlsx. I have created a calendar table CalendarTable which includes all dates between the minimum and maximum date in Sheet1[Date] and added calculated columns DateInt (integer of date), FiscalQuarter, FiscalYear, FiscalYearQuarter, FiscalYearQuarterFormatted and RollingFiscalYearQuarterFormatted. Note that FiscalYearQuarter = FiscalQuarter + FiscalYear*4. I also added the calculated columns DateInt, FiscalQuarter, FiscalYear, and FiscalYearQuarter to Sheet1, and created a relationship between CalendarTable[DateInt] and Sheet1[DateInt]. I am trying to calculate Net Promoter Score = % Promoters - % Detractors; a Promoter is a respondent with Q1 = 9 or 10 and a Detractor is a respondent with Q1 between 1 and 6. Here are the measures I am using to calculate rolling Net Promoter Score: Rolling_Average_Detractors = VAR LastFiscalYearQuarter = MAX(CalendarTable[FiscalYearQuarter]) VAR Filtered = FILTER(ALL(Sheet1),Sheet1[FiscalYearQuarter] >= LastFiscalYearQuarter - 3 && Sheet1[FiscalYearQuarter] <= LastFiscalYearQuarter && SELECTEDVALUE(Sheet1[SEGMENT]) = Sheet1[SEGMENT]) RETURN COUNTROWS(FILTER(Filtered,[Q1]>=0&&[Q1]<=6))/COUNTX(Filtered,[Q1])*100 Rolling_Average_Promoters = VAR LastFiscalYearQuarter = MAX(CalendarTable[FiscalYearQuarter]) VAR Filtered = FILTER(ALL(Sheet1),Sheet1[FiscalYearQuarter] >= LastFiscalYearQuarter - 3 && Sheet1[FiscalYearQuarter] <= LastFiscalYearQuarter && SELECTEDVALUE(Sheet1[SEGMENT]) = Sheet1[SEGMENT]) RETURN COUNTROWS(FILTER(Filtered,[Q1]>=9&&[Q1]<=10))/COUNTX(Filtered,[Q1])*100 Rolling_Average_NPS = [Rolling_Average_Promoters] - [Rolling_Average_Detractors] I am applying two filters to the graph: Sheet1[SEGMENT ] = “Segment 1” because I only want to display Segment 1 data, and CalendarTable[FiscalYearQuarter] to display only data for Fiscal Q1 2019 through Fiscal Q4 2020. Data exists for prior to Fiscal Q1 2019 and is used for calculating the rolling average of earlier quarters, but should not be displayed on the graph. For the measures Rolling_Average_Promoters and Rolling_Average_Detractors I added && SELECTEDVALUE(Sheet1[SEGMENT]) = Sheet1[SEGMENT] to add back the filter on SEGMENT which is removed by ALL. This measure works correctly, but the rolling average for Fiscal Q3 2020 is not displayed. I want that data point to be displayed as I am reproducing an existing report created in non-Power BI software in Power BI. Correct values for Rolling Fiscal Q4 2020 filtered by Segment 1 (data from Fiscal Q1 2020 to Fiscal Q4 2020): Promoters = 1/14 = 7.14%, Detractors = 9/14 = 64.29%, NPS = Promoters – Detractors = -57.14%. Correct values for Rolling Q3 2020 filtered by Segment 1 (data from Fiscal Q4 2019 to Fiscal Q3 2020): Promoters = 1/14 = 7.14%, Detractors = 8/14 = 57.14%, NPS = Promoters – Detractors = -50%. Rolling Q3 2020 is not displaying on the graph right now. I have tried the following solutions which do not work (for detractors measure, promoters measure is similar): Rolling_Average_Detractors = VAR LastFiscalYearQuarter = MAX(CalendarTable[FiscalYearQuarter]) VAR Filtered = FILTER(ALL(Sheet1),Sheet1[FiscalYearQuarter] >= LastFiscalYearQuarter - 3 && Sheet1[FiscalYearQuarter] <= LastFiscalYearQuarter) RETURN COUNTROWS(FILTER(Filtered,[Q1]>=0&&[Q1]<=6))/COUNTX(Filtered,[Q1])*100 This works correctly and Rolling Fiscal Q3 2020 is displayed, but this removes the filter on SEGMENT. Rolling_Average_Detractors = VAR LastFiscalYearQuarter = MAX(CalendarTable[FiscalYearQuarter]) VAR Filtered = FILTER(ALLEXCEPT(Sheet1,Sheet1[SEGMENT]),Sheet1[FiscalYearQuarter] >= LastFiscalYearQuarter - 3 && Sheet1[FiscalYearQuarter] <= LastFiscalYearQuarter) RETURN COUNTROWS(FILTER(Filtered,[Q1]>=0&&[Q1]<=6))/COUNTX(Filtered,[Q1])*100 This does not work properly and causes incorrect values to be displayed on the graph. Rolling_Average_Detractors = VAR LastFiscalYearQuarter = MAX(CalendarTable[FiscalYearQuarter]) VAR Filtered = FILTER(ALLSELECTED(Sheet1),Sheet1[FiscalYearQuarter] >= LastFiscalYearQuarter - 3 && Sheet1[FiscalYearQuarter] <= LastFiscalYearQuarter) RETURN COUNTROWS(FILTER(Filtered,[Q1]>=0&&[Q1]<=6))/COUNTX(Filtered,[Q1])*100 This does not work correctly if a filter is applied to the graph displaying only Q1 2019 to Q4 2020. What happens is that the rolling average for earlier quarters is calculated only including data from Q1 2019 and later. For example, the rolling average of Q1 2019 includes data from Q2 2018, Q3 2018 and Q4 2018 as well as Q1 2019 but if I use ALLSELECTED then it is excluded. See pbix file and Excel file posted to OneDrive: https://1drv.ms/u/s!AtKO3f2K35FzkfROX_Z5pmHMbKQqPA?e=EWDyDp Does anyone have any ideas?Moving Average Column Per Category
Need DAX formula to get the MOVING AVG PER CATEGORY column (highlighted in green) The result is average of top rows of a category i.e., Category A: Row 2 is average of EMP HEADCOUNT 314, 317 Row 3 is average of EMP HEADCOUNT 314, 317 and 319 and so on. This should go on groupby category Category B: Row 7 is average of 16, 15 Row 8 is average of 16, 15 ,13 Category C: Row 12 is average of 27, 29 I hope i explained this correctly. Please reply for any further clarifications. Thanks.
