Nested IF/AND statements in a calculated column

Question

I'm having some trouble with nested IF statements in a calcualted column on my date table.&nbsp; 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.&nbsp;For the life of me, I can't see why the following formula is not calculating correctly.&nbsp; If the date is 8th January, it should surely provide the value of&nbsp;_Date[Z_FW] (in this case, 52) as the first condition is true (year = 2023, date is less than 31st Jan).&nbsp; Instead, it is applying the condition of the second, untrue statement giving the value -3 (_Date[Week of Year] -5).&nbsp;FinWeek =&nbsp; &nbsp; &nbsp;IF(&nbsp; &nbsp; &nbsp; &nbsp; AND(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; _Date[FYr] = 2023,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; _Date[Date] &lt;= DATE(31,01,2023)),&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; _Date[Z_FW],&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; IF (&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; AND(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; _Date[FYr] = 2023&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ,&nbsp;_Date[Date] &gt;= DATE(01,02,2023)),&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; _Date[Week of Year] -5,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; IF(&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; AND(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; _date[date] &gt;= DATE(01,02,2022),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; _date[date] &lt;= DATE(06,02,2022)),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;_Date[Z_FW]&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; )&nbsp; &nbsp; &nbsp; &nbsp; )&nbsp; &nbsp; )&nbsp;Can anyone help me debug this?&nbsp; I'm at a loss, and must be missing something fundamental...

anil_kapkoti · Accepted Answer

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

Forum Discussion

Nested IF/AND statements in a calculated column

2 Replies

Related content

Power Query Nested IF AND Statement

Nested IF/AND Statement

Multiple nested IF statements

DAX Measure with Nested IF Statements

Nested switch statements?