Forum Discussion

inglexjc's avatar
inglexjc
Post Patron
2 years ago
Solved

Code to show case active when there is a future date

I have a report that uses this code to show if a case is active or not:

 

End of Service Date/Open = SWITCH(
IF(
ISBLANK(PBI_TR_Placements_OOH[SERVICE_END_DATE]),0,
PBI_TR_Placements_OOH[SERVICE_END_DATE]),0,"Active",
PBI_TR_Placements_OOH[SERVICE_END_DATE],
FORMAT(PBI_TR_Placements_OOH[SERVICE_END_DATE],"Short Date"))
 
Issue is sometimes there are future dates entered into the Service_end_date and the case is then showing closed.  I need it to show Active when the date is in the future.
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi inglexjc ,

    Please try to create a new column with below dax formula:

    Column =
VAR _a =
    IF ( ISBLANK ( [Date] ), 0, [Date] )
VAR _b =
    SWITCH (
        TRUE (),
        _a = 0, "Active",
        NOT ( ISBLANK ( [Date] ) )
            && [Date] > TODAY (), "Active",
        FORMAT ( [Date], "short date" )
    )
RETURN
    _b

    Please refer the attached .pbxi file.

     

    Best regards,
    Community Support Team_Binbin Yu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. 

6 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    2 years ago

    inglexjc , I have created an active/current employee measure using start and end dates, which should help

     

    Power BI: HR Analytics - Employees as on Date: https://youtu.be/e6Y-l_JtCq4
    https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970
    Power BI HR Active Employee Tenure Bucketing, and Hired, Terminated, and Active employees: https://youtu.be/fvgcx8QLqZU

     

    The sample file is attached

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi inglexjc ,

    In order to better understand your demands and give the right solution, could you please provide some more specific information? such as your desensitized example data and a screenshot of your desired results?

    Thanks for your efforts & time in advance.

     

    Best regards,
    Community Support Team_Binbin Yu

    • inglexjc's avatar
      inglexjc
      Post Patron
      2 years ago

      So my current table with code 

      End of Service Date/Open = SWITCH(
      IF(
      ISBLANK(PBI_TR_Placements_OOH[SERVICE_END_DATE]),0,
      PBI_TR_Placements_OOH[SERVICE_END_DATE]),0,"Active",
      PBI_TR_Placements_OOH[SERVICE_END_DATE],
      FORMAT(PBI_TR_Placements_OOH[SERVICE_END_DATE],"Short Date"))

       

      Comes up with these results.  But I need all the dates that are in the future to show "Active" as well.  So example listed 11/1/2023 and 1/1/2024 need to read "Active".

      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago

        Hi inglexjc ,

        Please try to create a new column with below dax formula:

         

        End of Service Date/Open =
VAR _a =
    IF (
        ISBLANK ( PBI_TR_Placements_OOH[SERVICE_END_DATE] ),
        0,
        PBI_TR_Placements_OOH[SERVICE_END_DATE]
    )
VAR _b =
    SWITCH (
        TRUE (),
        _a = 0, "Active",
        PBI_TR_Placements_OOH[SERVICE_END_DATE] > TODAY (), "Active",
        PBI_TR_Placements_OOH[SERVICE_END_DATE], FORMAT ( PBI_TR_Placements_OOH[SERVICE_END_DATE], "Short Date" )
    )
RETURN
    _b

         

         

        Best regards,
        Community Support Team_Binbin Yu
        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.