topic Re: Add extension column with If Statement in DAX Commands and Tips

Add extension column with If Statement

Ada_Danelle — Thu, 12 Jan 2023 14:52:29 GMT

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?

Re: Add extension column with If Statement

SivaMani — Thu, 12 Jan 2023 20:18:33 GMT

Ada_Danelle, Can you share your table structure and sample data?

Re: Add extension column with If Statement

Anonymous — Fri, 13 Jan 2023 07:04:21 GMT

Hi Ada_Danelle ,

I created a sample pbix file(see the attachment), please check if that is what you want. Please update the formula of calculated table as below:

Calendar = ADDCOLUMNS ( CALENDAR ( IF ( MONTH ( TODAY () ) < 10, DATE ( YEAR ( TODAY () ) - 1, 10, 1 ), DATE ( YEAR ( TODAY () ), 10, 1 ) ), IF ( MONTH ( TODAY () ) < 10, DATE ( YEAR ( TODAY () ), 3, 31 ), DATE ( YEAR ( TODAY () ) + 1, 3, 31 ) ) ), "Type", "HTD2 (Oct - Mar) ", "Order", 3 )

Best Regards

Re: Add extension column with If Statement

Ada_Danelle — Fri, 13 Jan 2023 08:35:15 GMT

@yingyinr it works perfectly, thank you so much.