topic Nested IF/AND statements in a calculated column in DAX Commands and Tips

Nested IF/AND statements in a calculated column

nmcge — Mon, 07 Aug 2023 14:33:08 GMT

I'm having some trouble with nested IF statements in a calcualted column on my date table. Essentially, I need a fiscal year that starts at the beginning of February and runs until 31st January, maintaining 52 weeks - the result of which leaves an unusual number of days in the final week of of one year and the beginning week of the next.

For the life of me, I can't see why the following formula is not calculating correctly. If the date is 8th January, it should surely provide the value of _Date[Z_FW] (in this case, 52) as the first condition is true (year = 2023, date is less than 31st Jan). Instead, it is applying the condition of the second, untrue statement giving the value -3 (_Date[Week of Year] -5).

FinWeek =

IF(

AND(

_Date[FYr] = 2023,

_Date[Date] <= DATE(31,01,2023)),

_Date[Z_FW],

IF (

AND(

_Date[FYr] = 2023

, _Date[Date] >= DATE(01,02,2023)),

_Date[Week of Year] -5,

IF(

AND(

_date[date] >= DATE(01,02,2022),

_date[date] <= DATE(06,02,2022)),

_Date[Z_FW]

)

Can anyone help me debug this? I'm at a loss, and must be missing something fundamental...

Re: Nested IF/AND statements in a calculated column

Anil_kapkoti — Mon, 07 Aug 2023 17:12:02 GMT

It Seems that you have an issue in the date values,
Try This
FinWeek =
IF(
AND(
_Date[FYr] = 2023,
_Date[Date] <= DATE(2023, 01, 31)
),
_Date[Z_FW],
IF (
AND(
_Date[FYr] = 2023,
_Date[Date] >= DATE(2023, 02, 01)
),
_Date[Week of Year] - 5,
IF(
AND(
_Date[Date] >= DATE(2022, 02, 01),
_Date[Date] <= DATE(2022, 02, 06)
),
1,
_Date[Z_FW]
)
)
)

Re: Nested IF/AND statements in a calculated column

nmcge — Tue, 08 Aug 2023 08:01:17 GMT

Haha, can't believe I couldn't see that. I must have had a tough day yesterday.

Really appreciate it! Thank you.