Forum Discussion
Helper IDate table with Financial year date
hi there i have created a date table as follows and i need to add the Financial year as a column to it. I tried the below FY measure however i keep getting an errorerror on FY column
Date =
ADDCOLUMNS (
CALENDAR (DATE(2000;1;1); DATE(2025;12;31));
"DateAsInteger"; FORMAT ( [Date]; "YYYYMMDD" );
"Year"; YEAR ( [Date] );
"Monthnumber"; FORMAT ( [Date]; "MM" );
"YearMonthnumber"; FORMAT ( [Date]; "YYYY/MM" );
"YearMonthShort"; FORMAT ( [Date]; "YYYY/mmm" );
"MonthNameShort"; FORMAT ( [Date]; "mmm" );
"MonthNameLong"; FORMAT ( [Date]; "mmmm" );
"DayOfWeekNumber"; WEEKDAY ( [Date] );
"DayOfWeek"; FORMAT ( [Date]; "dddd" );
"DayOfWeekShort"; FORMAT ( [Date]; "ddd" );
"Quarter"; "Q" & FORMAT ( [Date]; "Q" );
"YearQuarter"; FORMAT ( [Date]; "YYYY" ) & "/Q" & FORMAT ( [Date]; "Q" )
)FY = IF(Month('Date'[Date]) <= 6, 'Date'[Year] , 'Date'[Year] + 1)
RedDragon , Please get the calendar from this link
There 12 calendars starting from each month
Any week related - https://community.powerbi.com/t5/Community-Blog/Any-Weekday-Week-Decoding-Date-and-Calendar-2-5-Power-BI-Turning/ba-p/1187482
try like
FY = IF(Month('Date'[Date]) <= 6; 'Date'[Year] ; 'Date'[Year] + 1)Hi RedDragon
amitchandak,nandukrishnavs, Karlos has show you the way to add the column after building Date Table.
You can build FY column directly in Date table measure.
Date = ADDCOLUMNS ( CALENDAR (DATE(2000;1;1); DATE(2025;12;31)); "DateAsInteger"; FORMAT ( [Date]; "YYYYMMDD" ); "Year"; YEAR ( [Date] ); "Monthnumber"; FORMAT ( [Date]; "MM" ); "YearMonthnumber"; FORMAT ( [Date]; "YYYY/MM" ); "YearMonthShort"; FORMAT ( [Date]; "YYYY/mmm" ); "MonthNameShort"; FORMAT ( [Date]; "mmm" ); "MonthNameLong"; FORMAT ( [Date]; "mmmm" ); "DayOfWeekNumber"; WEEKDAY ( [Date] ); "DayOfWeek"; FORMAT ( [Date]; "dddd" ); "DayOfWeekShort"; FORMAT ( [Date]; "ddd" ); "Quarter"; "Q" & FORMAT ( [Date]; "Q" ); "YearQuarter"; FORMAT ( [Date]; "YYYY" ) & "/Q" & FORMAT ( [Date]; "Q" ); "FY";IF(MONTH([Date])<=6;YEAR ( [Date] );YEAR ( [Date] )+1) )Result:
You can download the pbix file from this link: Date table with Financial year date
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
5 Replies
- amitchandak
Super User
RedDragon , Please get the calendar from this link
There 12 calendars starting from each month
Any week related - https://community.powerbi.com/t5/Community-Blog/Any-Weekday-Week-Decoding-Date-and-Calendar-2-5-Power-BI-Turning/ba-p/1187482
try like
FY = IF(Month('Date'[Date]) <= 6; 'Date'[Year] ; 'Date'[Year] + 1) - nandukrishnavs
Community Champion
The issue is with the delimiter.
If your machine follows semicolon (;) as the delimiter then try below DAX.
FY = IF(Month('Date'[Date]) <= 6; 'Date'[Year] ; 'Date'[Year] + 1)If your Machine follows comma (,) as the delimiter then try to use below DAX.
Date = ADDCOLUMNS ( CALENDAR ( DATE ( 2000, 1, 1 ), DATE ( 2025, 12, 31 ) ), "DateAsInteger", FORMAT ( [Date], "YYYYMMDD" ), "Year", YEAR ( [Date] ), "Monthnumber", FORMAT ( [Date], "MM" ), "YearMonthnumber", FORMAT ( [Date], "YYYY/MM" ), "YearMonthShort", FORMAT ( [Date], "YYYY/mmm" ), "MonthNameShort", FORMAT ( [Date], "mmm" ), "MonthNameLong", FORMAT ( [Date], "mmmm" ), "DayOfWeekNumber", WEEKDAY ( [Date] ), "DayOfWeek", FORMAT ( [Date], "dddd" ), "DayOfWeekShort", FORMAT ( [Date], "ddd" ), "Quarter", "Q" & FORMAT ( [Date], "Q" ), "YearQuarter", FORMAT ( [Date], "YYYY" ) & "/Q" & FORMAT ( [Date], "Q" ) )FY = IF(Month('Date'[Date]) <= 6, 'Date'[Year] , 'Date'[Year] + 1)
Did I answer your question? Mark my post as a solution!
Appreciate with a kudos 🙂 Hi RedDragon
amitchandak,nandukrishnavs, Karlos has show you the way to add the column after building Date Table.
You can build FY column directly in Date table measure.
Date = ADDCOLUMNS ( CALENDAR (DATE(2000;1;1); DATE(2025;12;31)); "DateAsInteger"; FORMAT ( [Date]; "YYYYMMDD" ); "Year"; YEAR ( [Date] ); "Monthnumber"; FORMAT ( [Date]; "MM" ); "YearMonthnumber"; FORMAT ( [Date]; "YYYY/MM" ); "YearMonthShort"; FORMAT ( [Date]; "YYYY/mmm" ); "MonthNameShort"; FORMAT ( [Date]; "mmm" ); "MonthNameLong"; FORMAT ( [Date]; "mmmm" ); "DayOfWeekNumber"; WEEKDAY ( [Date] ); "DayOfWeek"; FORMAT ( [Date]; "dddd" ); "DayOfWeekShort"; FORMAT ( [Date]; "ddd" ); "Quarter"; "Q" & FORMAT ( [Date]; "Q" ); "YearQuarter"; FORMAT ( [Date]; "YYYY" ) & "/Q" & FORMAT ( [Date]; "Q" ); "FY";IF(MONTH([Date])<=6;YEAR ( [Date] );YEAR ( [Date] )+1) )Result:
You can download the pbix file from this link: Date table with Financial year date
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi RedDragon
Could you tell me if your problem has been solved? If it is, kindly Accept the helpful reply as the solution. More people will benefit from it. Or you are still confused about it, please provide me with more details about your problem or share me with your pbix file from your Onedrive for Business.
Best Regards,
Rico Zhou
