YEAR FUNCTION AND THREE YEAR AVERAGE

[MS3]

Everyone, Did a calendar as per below: Calendar = VAR Days = CALENDAR ( DATE ( 2017, 1, 1 ), DATE ( 2019, 12, 31 ) ) RETURN ADDCOLUMNS ( Days, "Year", YEAR ( [Date] ), "Month Number", MONTH ( [Date] ), "Month", FORMAT ( [Date], "mmmm" ), "Year Month", FORMAT ( [Date], "mmm yy" ), "Quarter", FORMAT ( [Date], "\QQ" ) ) Then trying to do a 3 Year Average. The problem with the above is Year is DATE format. This allows when I put the YEAR in Columns to display correctly (e.g. 2017, 2018, 2019). However unless it is a whole number it does not calcualte the 3 year average correctly. Calendar = VAR Days = CALENDAR ( DATE ( 2017, 1, 1 ), DATE ( 2019, 12, 31 ) ) RETURN ADDCOLUMNS ( Days, "Year", YEAR ( [Date] ), "Month Number", MONTH ( [Date] ), "Month", FORMAT ( [Date], "mmmm" ), "Year Month", FORMAT ( [Date], "mmm yy" ), "Quarter", FORMAT ( [Date], "\QQ" ) ) How do I make both things work TO_3YR = DIVIDE ( CALCULATE ( SUM ( TO_FS_Master[Amount] ), DATESINPERIOD ( 'Calendar'[Year], MAX ( 'Calendar'[YEAR] ), -3, YEAR ) ), CALCULATE ( DISTINCTCOUNT ( 'Calendar'[YEAR] ), DATESINPERIOD ( Calendar[YEAR], MAX ( Calendar[YEAR] ), -3, YEAR ) ) ) If year is a DATE format, i t displays wrongly when used as a column (For 01.01.2017 it displays 09.07.1905). If year is a WHOLE NUMBER format the years display correctly (2017/ 2018 and 2019) but the 3YR AVERAGE formula does not work. What is the way around this?

[PaulDBrown]

@MS3 

Apologies, call me simple, but you lost me on line ... it reads a bit like a live narrative of a thought process...
how about you break down your question into separate measures, questions, sample dataset, model depiction, and if possible, a PBIX file?