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.

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 😞