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.
HI all, I've created a CALENDAR and can get everything functioning normally except when incrementing the "YY" by +1 in the following formula.
Solved! Go to Solution.
Hi @Jack2of3 ,
To update your formula as below.
DateTable = ADDCOLUMNS ( CALENDAR ( DATE ( 2000, 07, 01 ), DATE ( 2035, 06, 30 ) ), "Day of Month", DAY ( [Date] ), "Day", FORMAT ( [Date], "DDDD" ), "Year", FORMAT ( [Date], "YYYY" ), "Month", FORMAT ( [Date], "MMMM" ), "Period", MONTH ( [Date] ), "PerName", UPPER ( FORMAT ( [Date], "MMM" ) ) & "-" & FORMAT ( [Date], "yy" ), "Quarter", SWITCH ( MONTH ( [Date] ), 1, "Qtr1", 2, "Qtr1", 3, "Qtr1", 4, "Qtr2", 5, "Qtr2", 6, "Qtr2", 7, "Qtr3", 8, "Qtr3", 9, "Qtr3", 10, "Qtr4", 11, "Qtr4", "Qtr4" ), "FisYear", SWITCH ( TRUE (), MONTH ( [Date] ) < 7, YEAR ( [Date] ), YEAR ( [Date] ) + 1 ), "FisQtr", SWITCH ( MONTH ( [Date] ), 1, "Qtr3", 2, "Qtr3", 3, "Qtr3", 4, "Qtr4", 5, "Qtr4", 6, "Qtr4", 7, "Qtr1", 8, "Qtr1", 9, "Qtr1", 10, "Qtr2", 11, "Qtr2", "Qtr2" ), "FisPer", SWITCH ( TRUE (), MONTH ( [Date] ) < 7, MONTH ( [Date] ) + 6, MONTH ( [Date] ) - 6 ), "FisPerName", UPPER ( FORMAT ( [Date], "MMM" ) ) & "-" & IF ( SWITCH ( TRUE (), MONTH ( [Date] ) < 7, FORMAT ( [Date], "yy" ), FORMAT ( [Date], "yy" ) ) + 1 >= 10, SWITCH ( TRUE (), MONTH ( [Date] ) < 7, FORMAT ( [Date], "yy" ), FORMAT ( [Date], "yy" ) ) + 1, 0 & SWITCH ( TRUE (), MONTH ( [Date] ) < 7, FORMAT ( [Date], "yy" ), FORMAT ( [Date], "yy" ) ) + 1 ) )
Hi @Jack2of3 ,
To update your formula as below.
DateTable = ADDCOLUMNS ( CALENDAR ( DATE ( 2000, 07, 01 ), DATE ( 2035, 06, 30 ) ), "Day of Month", DAY ( [Date] ), "Day", FORMAT ( [Date], "DDDD" ), "Year", FORMAT ( [Date], "YYYY" ), "Month", FORMAT ( [Date], "MMMM" ), "Period", MONTH ( [Date] ), "PerName", UPPER ( FORMAT ( [Date], "MMM" ) ) & "-" & FORMAT ( [Date], "yy" ), "Quarter", SWITCH ( MONTH ( [Date] ), 1, "Qtr1", 2, "Qtr1", 3, "Qtr1", 4, "Qtr2", 5, "Qtr2", 6, "Qtr2", 7, "Qtr3", 8, "Qtr3", 9, "Qtr3", 10, "Qtr4", 11, "Qtr4", "Qtr4" ), "FisYear", SWITCH ( TRUE (), MONTH ( [Date] ) < 7, YEAR ( [Date] ), YEAR ( [Date] ) + 1 ), "FisQtr", SWITCH ( MONTH ( [Date] ), 1, "Qtr3", 2, "Qtr3", 3, "Qtr3", 4, "Qtr4", 5, "Qtr4", 6, "Qtr4", 7, "Qtr1", 8, "Qtr1", 9, "Qtr1", 10, "Qtr2", 11, "Qtr2", "Qtr2" ), "FisPer", SWITCH ( TRUE (), MONTH ( [Date] ) < 7, MONTH ( [Date] ) + 6, MONTH ( [Date] ) - 6 ), "FisPerName", UPPER ( FORMAT ( [Date], "MMM" ) ) & "-" & IF ( SWITCH ( TRUE (), MONTH ( [Date] ) < 7, FORMAT ( [Date], "yy" ), FORMAT ( [Date], "yy" ) ) + 1 >= 10, SWITCH ( TRUE (), MONTH ( [Date] ) < 7, FORMAT ( [Date], "yy" ), FORMAT ( [Date], "yy" ) ) + 1, 0 & SWITCH ( TRUE (), MONTH ( [Date] ) < 7, FORMAT ( [Date], "yy" ), FORMAT ( [Date], "yy" ) ) + 1 ) )
Thank you very much. I was banging my head trying to figure that out.
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 |
---|---|
107 | |
99 | |
76 | |
64 | |
58 |
User | Count |
---|---|
148 | |
113 | |
97 | |
84 | |
67 |