Forum Discussion

siddrow's avatar
siddrow
Icon for Helper III rankHelper III
4 years ago
Solved

Help with converting excel formula to Power BI

Hi

 

I'm after some help to convert the below excel formula so I can create a custom column in my Power BI query

 

 

 =IFS(
    AND(ISNUMBER([@[Date Completed]]),ISBLANK([@Status])), "Missing status",
    [@Status] <> "completed", "",
    ISBLANK([@[Date Completed]]), "Missing completed date",
    AND(ISNUMBER([@[Date Completed]]),[@[Date Completed]]<=[@[Due Date]]), "Completed on time",
    TRUE, "Completed late"
)

 

Need Help
  • v-xiaotang's avatar
    v-xiaotang
    4 years ago

    Hi siddrow 

    Thanks for reaching out to us.

    You can try this measure

    Measure = 
SWITCH(TRUE(),
MIN('Table'[Status])=BLANK(),"Missing status",
MIN('Table'[Status])<>"completed","",
MIN('Table'[Date Completed])=BLANK(),"Missing completed date",
MIN('Table'[Date Completed])<=MIN('Table'[Due Date]),"Completed on time",
"Completed late"
)

     

    Best Regards,

    Community Support Team _Tang

    If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

3 Replies

    • siddrow's avatar
      siddrow
      Icon for Helper III rankHelper III
      4 years ago

      Hi

       

      Please see below. 

       

      Due DateDate CompletedStatusComment

      1/01/20221/01/2022CompletedCompleted on time
      1/01/202231/12/2021CompletedCompleted on time
      1/01/2022 CompletedMissing completed date
      1/01/20222/02/2022CompletedCompleted late
      1/01/20223/03/2022 Missing status
      1/01/202231/12/2021Blabla 
      1/01/2022   
  • v-xiaotang's avatar
    v-xiaotang
    Icon for Community Support rankCommunity Support
    4 years ago

    Hi siddrow 

    Thanks for reaching out to us.

    You can try this measure

    Measure = 
SWITCH(TRUE(),
MIN('Table'[Status])=BLANK(),"Missing status",
MIN('Table'[Status])<>"completed","",
MIN('Table'[Date Completed])=BLANK(),"Missing completed date",
MIN('Table'[Date Completed])<=MIN('Table'[Due Date]),"Completed on time",
"Completed late"
)

     

    Best Regards,

    Community Support Team _Tang

    If this post helps, please consider Accept it as the solution to help the other members find it more quickly.