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
daXtreme's avatar
daXtreme
Icon for Solution Sage rankSolution 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