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 mo...
  • 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:

     

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 🙂