Forum Discussion

StevenHarrison's avatar
StevenHarrison
Icon for Resolver I rankResolver I
4 years ago
Solved

Dates - Future, Past and Blank (null) - conditional formatting

Hi Guys - I have a [NEXT DATE] column with Future, Past and null dates (in Power Query Editor displayed as null and as (Blank) in Data view).

I want to be able to conditionally format a table visualisation to display

  1. Future dates as 'Not Due'
  2. Past dates as 'Overdue'
  3. null or blank as 'N/A'

any thoughts on a DAX or power query solution appreciated

Need Help
  • StevenHarrison's avatar
    StevenHarrison
    4 years ago

    Hi - Probably did this the long way round but created a custom column called Due Service?

    New 'Due?' column in the data view:

    Then used that for the conditioning on the NEXT DATE column:

    This seems to work, thanks for the feedback.

     

     

     

7 Replies

  • Samarth_18's avatar
    Samarth_18
    Icon for Community Champion rankCommunity Champion
    4 years ago

    Hi StevenHarrison ,

     

    You could create a column as below:-

    column =
SWITCH (
    TRUE (),
    [next date] < TODAY (), "Overdue",
    [next date] >= TODAY (), "Not Due",
    ISBLANK ( [next date] ), "N/A"
)

    BR,

    Samarth

    • StevenHarrison's avatar
      StevenHarrison
      Icon for Resolver I rankResolver I
      4 years ago

      Hi Samarth - The entry for the ISBLANK entry returns Overdue:

       

       

       

       

      • Samarth_18's avatar
        Samarth_18
        Icon for Community Champion rankCommunity Champion
        4 years ago

        StevenHarrison ,Please try this:-

        column =
SWITCH (
    TRUE (),
    [next date] < TODAY (), "Overdue",
    [next date] >= TODAY (), "Not Due",
    [next date] = BLANK() , "N/A"
)