Rolling Quarterly Average - Custom Fiscal Year (Nov - Oct)

[Mehal1996]

Hi there,

I'm hoping I can find a solution on the below:

I have created an Average for my numerical variable (Date Diff) which I can effectively aggregate to my FiscalQuarter date variable which is in a seperate Calendar table. I have used the below Measure for this:

Average Date Diff = AVERAGE('DateTable'[Date Diff]) +0

I am now looking to calculate a Quarterly Rolling Average which I can add onto a Line Chart visual. Below shows what I currently have in table view. The values are averaged and aggregated on a Quarterly basis for the Fiscal Year that I have applied a filter on (FY22). I would like there to be a rolling average which I can plot, therefore, FQ1 would have 60 and then FQ2 would have 107.5 and then FQ3 would have 154.41 etc.

Thanks for any help on this. 

[v-rongtiep-msft · Accepted solution]

Hi @Mehal1996 ,

I have created a simple sample, please refer to it to see if it helps you.

Create a meaure.

Measure =
CALCULATE (
    SUM ( 'Table'[datediff] ),
    FILTER (
        ALL ( 'Table' ),
        'Table'[Index] <= SELECTEDVALUE ( 'Table'[Index] )
            && 'Table'[YEAR] = SELECTEDVALUE ( 'Table'[YEAR] )
    )
)

If I have misunderstood your meaning, please provide more details with the desired output and pbix file without privay information.

Best Regards

Community Support Team _ Polly

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@Mehal1996 , You need measure like

YTD Sales = CALCULATE(AverageX(Values(Date[FY Qtr]), [Average Date Diff]) ,DATESYTD('Date'[Date],"10/31"))

or rolling

Cumm = CALCULATE(AverageX(Values(Date[FY Qtr]), [Average Date Diff]) ,filter(all('Date'),'Date'[date] <=max('Date'[date])))

[Mehal1996]

Hi Amit,

Thanks for the response here. 

I tried both and they didn't seem to work for me. For the first solution, I get the below error:

Column 'RowNumber-2662979B-1795-4F74-8F37-6A1BA8059B61' in table 'Calendar' cannot be found or may not be used in this expression.

For the second solution, I get the below output shown in the 3rd column of the table in the image below:

Hopefully I am not doing something wrong.