data view
1 Topic
Add extension column with If Statement
Good day experts I am trying to add a column to an existing table in the data view using a DAX query. The requirement is to determine the second half of the fiscal year using today's date. If the date falls in this 2nd half of the year, the term "HTD2 (Oct - Mar) " must be added in the Type column, and the value 3 in the Order column. As the fiscal year stretches from April to March, the second half falls over the change from one calendar year to the next. With the currently used calculation, if the current month is Oct, Nov or Dec, the calculation is incorrect. The current calculation is as follows: ADDCOLUMNS( CALENDAR( (DATE(YEAR(TODAY())-1,10,1 )), (DATE(YEAR(TODAY()),3,30 ))) , "Type", "HTD2 (Oct - Mar) ", "Order", 3) I am trying to create something like below, but my DAX knowledge is limited and I can't get it to work: IF(MONTH(TODAY())<10, ADDCOLUMNS( CALENDAR( (DATE(YEAR(TODAY())-1,10,1 )), (DATE(YEAR(TODAY()),3,30 ))) , "Type", "HTD2 (Oct - Mar) ", "Order", 3), ADDCOLUMNS( CALENDAR( (DATE(YEAR(TODAY()),10,1 )), (DATE(YEAR(TODAY()+1),3,30 ))) , "Type", "HTD2 (Oct - Mar) ", "Order", 3)) Could you please assist?Solved847Views0likes3Comments