Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Filtering by Financial Year

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.
Need Help
  • parry2k's avatar
    parry2k
    7 years ago

    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", "")
parry2k's avatar
parry2k
Super User
7 years ago

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

Anonymous's avatar
Anonymous
Not applicable
7 years ago

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's avatar
    parry2k
    Super User
    7 years ago

    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's avatar
      Anonymous
      Not applicable
      7 years ago

      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's avatar
        parry2k
        Super User
        7 years ago

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

         

        Date 2 = Your Table Name[Date1]