cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

YEAR FUNCTION AND THREE YEAR AVERAGE

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?

1 REPLY 1
Highlighted
Super User II
Super User II

Re: YEAR FUNCTION AND THREE YEAR AVERAGE

@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? 





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Upcoming Events

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors