topic Re: Date Function - DAX - Create new column from an existing date column in DAX Commands and Tips

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

Anonymous — Fri, 19 Aug 2022 04:35:28 GMT

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 = Year (Reference to the table [specific column consisting dates])

Can anyone suggest how to create a column with a fiscal column output - "2021-22" from a specifc column consisting dates on same table.

Any help would be much appreciated.

thanks

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

PurpleGate — Fri, 19 Aug 2022 07:54:29 GMT

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",
" ")))))

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

daXtreme — Fri, 19 Aug 2022 09:27:40 GMT

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.

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

daXtreme — Fri, 19 Aug 2022 09:30:31 GMT

Fiscal years more often than not do not align with the usual calendar dates. If you have a functional rule that tells you how to create a fiscal value from the usual date, then you should code it appropriately (but the details are specific and without this knowledge nobody can give you formula). Fiscal calendars of any flavour (created in DAX or Power Query) can easily be found via a Google search. You can even start here -> www.sqlbi.com.

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

Anonymous — Mon, 22 Aug 2022 03:53:30 GMT

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

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

Anonymous — Mon, 22 Aug 2022 06:30:12 GMT

This issue has now been resolved.