Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
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?
Proud to be a Super User!
Paul on Linkedin.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
98 | |
86 | |
70 | |
62 |
User | Count |
---|---|
151 | |
120 | |
103 | |
87 | |
68 |