Add extension column with If Statement

[Ada_Danelle]

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?

[Anonymous · Accepted solution]

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

[Ada_Danelle]

@yingyinr it works perfectly, thank you so much.

[SivaMani]

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