Forum Discussion

nmcge's avatar
nmcge
Frequent Visitor
3 years ago
Solved

Nested IF/AND statements in a calculated column

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, ma...
  • Anil_kapkoti's avatar
    Anil_kapkoti
    3 years ago

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

Anil_kapkoti's avatar
Anil_kapkoti
New Member
3 years ago

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's avatar
    nmcge
    Frequent Visitor
    3 years ago

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

     

    Really appreciate it!  Thank you.