Forum Discussion

Ada_Danelle's avatar
Ada_Danelle
New Member
3 years ago
Solved

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?
ADDCOLUMNS
data view
DAX
  • Anonymous's avatar
    Anonymous
    3 years ago

    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

3 Replies