Rolling Average FYTD

Question

Hi Community. Looking for some help with the below measure code.&nbsp;I'm trying to return the FYTD for the months which have elapsed in this current financial year to date.Currently, the measure is summing up the average for each month and then&nbsp; returning the total. However i want the measure to acknowledge that only 2 months of the financial year have passed and calculate the average based on the total average (the sum of the average per month)/2 (the months elapsed this financial year, excluding current month).&nbsp;Can anyone recommend how i can upgrade this measure to meet that need?Average Days per Personrolling average FY Month = 
IF(
	ISFILTERED('Calendar (DAX)'[Date]),
	VAR _LAST_DATE = ENDOFMONTH('Calendar (DAX)'[Date])
	VAR _DATE_PERIOD =  
		DATESBETWEEN(
			'Calendar (DAX)'[Date],
			STARTOFMONTH(DATEADD(_LAST_DATE, -12, MONTH)),
			ENDOFMONTH(DATEADD(_LAST_DATE, 1, MONTH))
		)
	RETURN
		AVERAGEX(
			CALCULATETABLE(
				SUMMARIZE(
					VALUES('Calendar (DAX)'),
					'Calendar (DAX)'[FiscalYear]
                    , 'Calendar (DAX)'[date]
				),
				_DATE_PERIOD
			),
			CALCULATE([Average Days per Person], ALL('Calendar (DAX)'[Date]))
		)
)

ashish_mathur · Answer

Hi,
Share some data and show the expected result.&nbsp; Also, please clarify when does your Financial Year start

anonymous · Answer

Hi thereThanks for the reply. The financial year begins 01 July 2019.&nbsp; The Rolling average column in the below is the execpted result.&nbsp;&nbsp;Financial YearFinancial Year MonthFinancial Year Month NumberAverage Days per PersonPeopleDaysRolling Average2020July11.0506115462452.82576.941.0506115462020August20.6805040712444.11663.221.3908635822020September30&nbsp;0&nbsp;2020October40&nbsp;0&nbsp;2020November50&nbsp;0&nbsp;2020December60&nbsp;0&nbsp;2020January70&nbsp;0&nbsp;2020February80&nbsp;0&nbsp;2020March90&nbsp;0&nbsp;2020April100&nbsp;0&nbsp;2020May110&nbsp;0&nbsp;2020June120&nbsp;0&nbsp;&nbsp;

ashish_mathur · Answer

Hi,
How did you calculate the Rolling average of August as&nbsp;1.390863582?&nbsp; Please elaborate.&nbsp; Also, share the link from where i can download your PBI file.

anonymous · Answer

Thanks for this. I have a problem linking the pbix due to data sensitivity and not being able to deidentify the information.&nbsp;I can elaborate on the column "Rolling Average" using an excel example.&nbsp;To get the result for august, the results in column D for July and August are summed together and then divided by the number of months which are included to date in the financial year.The result for september would sum together cells D2:D4 and divide that by 3, given september is the 3rd month in the financial year in this example.&nbsp;&nbsp;I can't seem to create a measure in Power BI to sum together the average days per person and average that by the number of months elapsed on a rolling basis for the financial year.&nbsp;&nbsp;&nbsp;&nbsp;

ashish_mathur · Answer

Thank you for clarifying.&nbsp; I will need some data to work with so either share your PBI file or paste raw data in a format that i can paste in an Excel workbook.&nbsp; The data you shared in your earlier message is already the final result - I want the raw data input on which i can write formulas.

Forum Discussion

Rolling Average FYTD

6 Replies

Related content

Rolling 12M Average measure

3 Day Rolling Average - DAX

Rolling accumulation

Rolling accumulation

Conditional Rolling Average

Financial Year	Financial Year Month	Financial Year Month Number	Average Days per Person	People	Days	Rolling Average
2020	July	1	1.050611546	2452.8	2576.94	1.050611546
2020	August	2	0.680504071	2444.1	1663.22	1.390863582
2020	September	3	0		0
2020	October	4	0		0
2020	November	5	0		0
2020	December	6	0		0
2020	January	7	0		0
2020	February	8	0		0
2020	March	9	0		0
2020	April	10	0		0
2020	May	11	0		0
2020	June	12	0		0