Measure for rolling average by fiscal quarter with filters

[ItAccounts]

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?

[amitchandak · Accepted solution]

@ItAccounts , Pretty long description. Based on what I got

For custom qtr you need an additional column in the date table

Qtr Start Date = DATEADD(STARTOFYEAR('Date'[Date],"10/31"),QUOTIENT(DATEDIFF('Date'[Start Of Year], 'Date'[Date],MONTH),3)*3,MONTH)
Qtr Day = DATEDIFF('Date'[Qtr Start Date],'Date'[Date],Day)+1
Qtr Rank = RANKX(all('Date'),'Date'[Qtr Start date],,ASC,Dense)

Then you can use measures like 
This Qtr = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Qtr Rank]=max('Date'[Qtr Rank])))
Last Qtr = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Qtr Rank]=max('Date'[Qtr Rank])-1))

Last 4 Qtr = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Qtr Rank]>=max('Date'[Qtr Rank])-4 && 'Date'[Qtr Rank]<=max('Date'[Qtr Rank]) ))

Power BI — Qtr on Qtr with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-qtd-questions-time-intelligence-2-5-d842063da839
https://www.youtube.com/watch?v=8-TlVx7P0A0

refer to this approach if you need a trend https://www.youtube.com/watch?v=duMSovyosXE