Converting Excel Syntax to DAX - status based on two dates

[Aardvark99]

Hello Power BI Community!

I have some existing Excel syntax that has served me well, but I'm struggling to use it in Power BI.

I have a list of projects with a Start on Site Date (SOS) and a Grand Opening Date (GO).  I have previously used the below syntax to provide a status based on today's date; returning: TBC where the date is blank, Pending SOS, On Site, Complete.

=IF(E2="","TBC",IF(TODAY()<E2,"Pending SOS",IF(OR(E2>=TODAY(),TODAY()<=F2,F2=""),"On Site",IF(TODAY()>=F2,"Complete",))))

Where E2 = SOS Date
Where F2 = GO Date

Please can someone help me get this to work in Power BI?

[v-jianboli-msft]

Hi @Aardvark99 ,

If you want to use it for matrix visualisations, pie charts or page filters, you may need to create a calculated column:

Status = 
VAR _a =
    CALCULATE(MAX ( 'Table'[SOS Date] ))
VAR _b =
    CALCULATE(MAX ( 'Table'[GO Date] ))
RETURN
    SWITCH (
        TRUE (),
        ISBLANK ( _a ), "TBC",
        TODAY () < _a, "Pending SOS",
        OR ( TODAY () >= _a && TODAY () <= _b, TODAY () >= _a && ISBLANK ( _b ) ), "On Site",
        TODAY () >= _b, "Complete"
    )

Output:

Best Regards,

Jianbo Li

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

[Aardvark99]

Hi Jianbo Li,

The DAX works perfectly in a table. 

However, I can't seem to use it for matrix visualisations, pie charts or page filters... I must be doing something wrong 😞 

[v-jianboli-msft · Accepted solution]

Hi @Aardvark99 ,

Based on your description, I have created a simple sample:

Please try:

Measure =
VAR _a =
    MAX ( 'Table'[SOS Date] )
VAR _b =
    MAX ( 'Table'[GO Date] )
RETURN
    SWITCH (
        TRUE (),
        ISBLANK ( _a ), "TBC",
        TODAY () < _a, "Pending SOS",
        OR ( TODAY () >= _a && TODAY () <= _b, TODAY () >= _a && ISBLANK ( _b ) ), "On Site",
        TODAY () >= _b, "Complete"
    )

Final output:

Best Regards,

Jianbo Li

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