Nested IF/AND statements in a calculated column

[nmcge]

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)),

                    1,

               _Date[Z_FW]

            )

        )

    )

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

[Anil_kapkoti · Accepted solution]

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]
)
)
)

[nmcge]

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

Really appreciate it!  Thank you.