Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
Hello team BI,
I have a calendar table. See below.
I now need to have April - March as the 2017 FY year.... Similaily after that, 2018 FY, 2019 FY
Please can you assist me with the code:
Calendar =
ADDCOLUMNS (
CALENDAR (DATE(2016,4,1), DATE(2020,12,31)),
"Year", YEAR ( [Date] ),
"MonthNameShort", FORMAT ( [Date], "mmm" ),
"Quarter", "Q" & FORMAT ( [Date], "Q" ),
"YearQuarter", FORMAT ( [Date], "YYYY" ) & "/Q" & FORMAT ( [Date], "Q" )
)
Solved! Go to Solution.
Oh you mean a new column with the FY. Something like this? April 2016-March 2017 is FY 2017, right? Otherwise update the code in the last column. I added a month number too to simplify the code.
Calendar = ADDCOLUMNS ( ADDCOLUMNS ( CALENDAR ( DATE ( 2016, 4, 1 ), DATE ( 2020, 12, 31 ) ), "Year", YEAR ( [Date] ), "MonthNameShort", FORMAT ( [Date], "mmm" ), "MonthNumber", MONTH ( [Date] ), "Quarter", "Q" & FORMAT ( [Date], "Q" ), "YearQuarter", FORMAT ( [Date], "YYYY" ) & "/Q" & FORMAT ( [Date], "Q" ) ), "FY", IF ( [MonthNumber] >= 4, [Year] + 1, [Year] ) )
Yeah, you need to copy the code exactly as I wrote it for it to work. From what I see in your image, it's not the same. You're leaving the outer ADDCOLUMNS out, for instance.
Thank you. Happy Days
Hi
I would like my graph to show data only for that FY year. i.e.. 2018 = April 2017 to March 2018
The below shows from Jan - December
Thanks
Check what year the months shown belong to. Probably they are the correct ones (mix of 2017 and 2018) but just ordered from Jan to Dec.
Oh you mean a new column with the FY. Something like this? April 2016-March 2017 is FY 2017, right? Otherwise update the code in the last column. I added a month number too to simplify the code.
Calendar = ADDCOLUMNS ( ADDCOLUMNS ( CALENDAR ( DATE ( 2016, 4, 1 ), DATE ( 2020, 12, 31 ) ), "Year", YEAR ( [Date] ), "MonthNameShort", FORMAT ( [Date], "mmm" ), "MonthNumber", MONTH ( [Date] ), "Quarter", "Q" & FORMAT ( [Date], "Q" ), "YearQuarter", FORMAT ( [Date], "YYYY" ) & "/Q" & FORMAT ( [Date], "Q" ) ), "FY", IF ( [MonthNumber] >= 4, [Year] + 1, [Year] ) )
Hello,
Thanks for suppoorting
I keep on receiving the below error.
- The MonthNumber is now visible
- The FY year seems to show the error.
Thanks
J
Yeah, you need to copy the code exactly as I wrote it for it to work. From what I see in your image, it's not the same. You're leaving the outer ADDCOLUMNS out, for instance.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
If you love stickers, then you will definitely want to check out our Community Sticker Challenge!
User | Count |
---|---|
126 | |
78 | |
78 | |
60 | |
51 |
User | Count |
---|---|
164 | |
84 | |
68 | |
68 | |
58 |