Date table with Financial year date

[RedDragon]

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)

[amitchandak · Accepted solution]

@RedDragon , Please get the calendar from this link

There 12 calendars starting from each month

https://community.powerbi.com/t5/Community-Blog/Creating-Financial-Calendar-Decoding-Date-and-Calend...

Any week related - https://community.powerbi.com/t5/Community-Blog/Any-Weekday-Week-Decoding-Date-and-Calendar-2-5-Powe...

try like

FY = IF(Month('Date'[Date]) <= 6; 'Date'[Year] ; 'Date'[Year] + 1)

[v-rzhou-msft]

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

[v-rzhou-msft · Accepted solution]

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. 

[Anonymous]

Try 

FY = IF(Month('Date'[Date]) <= 6; 'Date'[Year]; 'Date'[Year] + 1)

[nandukrishnavs]

@RedDragon 

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 🙂

[amitchandak · Accepted solution]

@RedDragon , Please get the calendar from this link

There 12 calendars starting from each month

https://community.powerbi.com/t5/Community-Blog/Creating-Financial-Calendar-Decoding-Date-and-Calend...

Any week related - https://community.powerbi.com/t5/Community-Blog/Any-Weekday-Week-Decoding-Date-and-Calendar-2-5-Powe...

try like

FY = IF(Month('Date'[Date]) <= 6; 'Date'[Year] ; 'Date'[Year] + 1)