Forum Discussion

inglexjc's avatar
inglexjc
Icon for Post Patron rankPost 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[SERVIC...
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. 

inglexjc's avatar
inglexjc
Icon for Post Patron rankPost 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. 

  • inglexjc's avatar
    inglexjc
    Icon for Post Patron rankPost Patron
    2 years ago

    Anonymous  Thank you for your response.  I do get an error with this though:

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      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.