Code to show case active when there is a future date

[inglexjc]

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.

[Anonymous]

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]

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]

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]

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

[Anonymous · Accepted solution]

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. 

[amitchandak]

@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-tr...
Power BI HR Active Employee Tenure Bucketing, and Hired, Terminated, and Active employees: https://youtu.be/fvgcx8QLqZU

The sample file is attached