Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Converting Excel Syntax to DAX - status based on two dates

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's avatar
    v-jianboli-msft
    3 years ago

    Hi Anonymous ,

     

    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.

3 Replies

  • v-jianboli-msft's avatar
    v-jianboli-msft
    Community Support
    3 years ago

    Hi Anonymous ,

     

    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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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's avatar
    v-jianboli-msft
    Community Support
    3 years ago

    Hi Anonymous ,

     

    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.