Forum Discussion

matts29's avatar
matts29
Frequent Visitor
2 years ago
Solved

if/case statement

Hi 

 

Can someone please help.

 

I would like to write a logic in Power BI stating

 

If reviewdate is between today and the next 3 years then 1

If reviewdate is between today and the next 6 months then 2

If reviewdate is between today and the next 18 months then 3

 

all within the same column?

 

Thank you

  • FreemanZ's avatar
    FreemanZ
    2 years ago

    hi matts29 ,

     

    not sure if i fully get you, supposing you have a data table:

    reviewdate
    2/7/2024
    2/8/2024
    8/8/2024
    8/9/2024
    8/8/2025
    8/9/2025
    2/8/2027
    2/9/2027

     

    try to add a calculated column like:

    Column = 
SWITCH(
    TRUE(),
    data[reviewdate]<=EDATE(TODAY(), 6), 2,
    data[reviewdate]<=EDATE(TODAY(), 18), 3,
    data[reviewdate]<=EDATE(TODAY(), 36), 1,
    0
)

     

    it worked like:

     

2 Replies

  • FreemanZ's avatar
    FreemanZ
    Super User
    2 years ago

    hi matts29 ,

     

    not sure if i fully get you, supposing you have a data table:

    reviewdate
    2/7/2024
    2/8/2024
    8/8/2024
    8/9/2024
    8/8/2025
    8/9/2025
    2/8/2027
    2/9/2027

     

    try to add a calculated column like:

    Column = 
SWITCH(
    TRUE(),
    data[reviewdate]<=EDATE(TODAY(), 6), 2,
    data[reviewdate]<=EDATE(TODAY(), 18), 3,
    data[reviewdate]<=EDATE(TODAY(), 36), 1,
    0
)

     

    it worked like:

     

    • matts29's avatar
      matts29
      Frequent Visitor
      2 years ago

      Thanks thats brilliant! have  a good day 🙂