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 Gra...
  • 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.

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.