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