Filtering by Financial Year

[Anonymous]

I am trying to create a new column with financial year, i tried the following but it had the following error.

Can anyone advice me. Thanks so much in advance.

[Anonymous]

Hey @Anonymous 

The date column isn't displayed in your screenshot; maybe that column isn't formatted correctly? 

The other screenshot doesn't show the full expression, but the contents of your IF statements displayed in the screenshot look to be formatted properly. 

[Anonymous]

@Anonymous @parry2k 

The date column is showing error.

I am not sure about the data table id, only added a new column for the financial date ("date")
Is there anything else i need to do prior to this step?
Thanks so much in advance

[parry2k]

@Anonymous how you are adding date column? 

[Anonymous]

@parry2k 

under modelling->new column.

thanks in advance

[parry2k]

@Anonymous then what? what expression you are doing to add date

[Anonymous]

@parry2k 

i added the following into the forumla bar.

I am not sure if is it correct or it need to be done some other way?

my end result is i need to have a visual that filter by Financial Year.
any help & hint is appreicated.

[parry2k]

@Anonymous aha, you are adding date column which is new column and you are referring the same column in your DAX expression and that's the issue, replace Date with Date1 which I believe already exists in your table

Date = 
IF (
AND (
Date[Date1] >= DATE ( 2015, 4, 1 ),
Date[Date1] <= DATE ( 2016, 3, 31 )
),
"FY15",
IF (
AND (
Date[Date1] >= DATE ( 2016, 4, 1 ),
Date[Date1] <= DATE ( 2017, 3, 31 )
),
"FY16",
IF (
AND (
Date[Date1] >= DATE ( 2017, 4, 1 ),

Date[Date1] <= DATE ( 2018, 3, 31 )
),
"FY17",
IF (
AND (
Date[Date1] >= DATE ( 2018, 4, 1 ),
Date[Date1] >= DATE ( 2019, 3, 31 )
),
"FY18",
0
)
)
))

[Anonymous]

@parry2k 

i replaced the date with date1 but it still say "the syntax for '[date1] is incorrect.

my current date1 format is as shown in pic.
not sure where i have gone wrong.

[parry2k]

@Anonymous lets add a simple expression to make sure it is working, add new column with following expression

Date 2 = Your Table Name[Date1]

[Anonymous]

@parry2k 

yes, is working

[parry2k]

@Anonymous let's add one if condition in date and see if it works and then add the next add one and so on until you get where the issue is. I'm wondering last value you are return is 0 which should be string like "0" , but I don;t think that is the issue though.

[Anonymous]

@parry2k i added only 1 condition and is the same error "the syntax for [Date1] is incorrect.

[parry2k · Accepted solution]

I think I know what is going on, your table name need to be in single quote because its name is DAte, try following

Date = 
IF (
AND (
'Date'[Date1] >= DATE ( 2015, 4, 1 ),
'Date'[Date1] <= DATE ( 2016, 3, 31 )
),
"FY15", "")

[Anonymous]

it works! Thank so much! 

@parry2k 

[parry2k]

@Anonymous I guess your table name id Date and your column name is also Date, correct?