Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Date Function - DAX - Create new column from an existing date column

Hi I'm trying to create following columns using DAX from an existing date column on a table. 1. Fiscal_year_numer : Output -> 2021-22   I've created a "year" column with DAX like below: Year = Y...
Date Function
DAX Commands
Help required
PurpleGate's avatar
PurpleGate
Resolver III
4 years ago

You could use either a Switch statement, 

or a mega IF statement?

 

Fiscal Year = 
IF(AND([Year] =2012,[Month]<=12),"FY13",

IF(AND([Year] =2013,[Month]<=6),"FY13",
IF(AND([Year] =2013,[Month]<=12),"FY14",        
IF(AND([Year] =2014,[Month]<=6),"FY14",
IF(AND([Year] =2014,[Month]<=12),"FY15",
" ")))))
daXtreme's avatar
daXtreme
Solution Sage
4 years ago

PurpleGate 

 

Please, do not write code like this and do not advocate it. It's hard to read, hard to debug and hard to maintain. Instead, use SWITCH. Thanks.

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    daXtreme , I tried to create a column based on your recommendation. 

     

    Fiscal Year =

    VAR CurrYear = RIGHT(YEAR('Tab1'[Date]),2)
    --VAR LastYear = RIGHT(YEAR('Tab1'[Date]-1,2) //Applicable for previous FY
    VAR NewYear = RIGHT(YEAR('Tab1'[Date])+1,2)
    VAR FiscalYear =

    SWITCH(
    TRUE (),
    MONTH([Exam Date]) >= 6,
    "FY" & " " & CurrYear & "-" & NewYear
    --LastYear & "-" & CurrYear //Applicable for previous FY
    )
     
    RETURN
    FiscalYear
     
    FY calculation I wanted it to be - 1st Jul - 30th Jun
     
    There are some blank fields appearing in Fiscal Year column. Not sure why!
     
    Could you pls suggest if there's any issue with my dax?
     
    thanks