Forum Discussion

kormosb's avatar
kormosb
Helper III
6 years ago
Solved

EARLIER function

Hi Guys,

 

I am trying to use the EARLIER function as seen in the below picture, but it doesn't work. The poin is, that I am trying to get the solving day of the earlier date, if the "latest update date" is blank or 2020.03.26.

I wonder if you could help me get the expected result (2).

 

Thanks in advance,

Benjamin

Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    HI kormosb,

    You can try to use following calculate column formula, it can extract the last date and previous date to find out corresponding values:

     

    last date value =
VAR prevDate =
    CALCULATE (
        MAX ( 'Issue Navigator'[Latest refresh date] ),
        FILTER (
            'Issue Navigator',
            [Key] = EARLIER ( 'Issue Navigator'[Key] )
                && DATEVALUE ( [Latest refresh date] )
                    < DATEVALUE ( EARLIER ( 'Issue Navigator'[Latest refresh date] ) )
        )
    )
RETURN
    IF (
        'Issue Navigator'[Latest refresh date] = BLANK ()
            || DATEVALUE ( 'Issue Navigator'[Latest refresh date] )
                = DATEVALUE ( MAX ( 'Issue Navigator'[Latest refresh date] ) ),
        CALCULATE (
            MAX ( 'Issue Navigator'[Solving day (original)] ),
            FILTER (
                'Issue Navigator',
                [Key] = EARLIER ( 'Issue Navigator'[Key] )
                    && [Latest refresh date] = prevDate
            )
        ),
       'Issue Navigator'[Solving day (original)]
    )

     

    Regards,
    Xiaoxin Sheng

  • Anonymous's avatar
    Anonymous
    6 years ago

    HI kormosb,

    You can extract the variable and use in a new calculated column to compare with current date:

    Status =
VAR prevDate =
    CALCULATE (
        MAX ( 'Issue Navigator'[Latest refresh date] ),
        FILTER (
            'Issue Navigator',
            [Key] = EARLIER ( 'Issue Navigator'[Key] )
                && DATEVALUE ( [Latest refresh date] )
                    < DATEVALUE ( EARLIER ( 'Issue Navigator'[Latest refresh date] ) )
        )
    )
RETURN
    IF ( 'Issue Navigator'[Latest refresh date] >= prevDate, "Done" )

    Regards,

    Xiaoxin Sheng

8 Replies

  • az38's avatar
    az38
    Community Champion
    6 years ago

    Hi kormosb 

    I see a few columns in your statement that aren't presented in data example

    and please to facilitate help you copy-paste your example (both statement and data example) as text, not as screenshots

  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    Create new columns like

    max last date = maxx(filter(table, table[date]<earlier(Table[date]) && not(isbalnk(Table[value]))),Table[date])
last date value =maxx(filter(table, table[date]=earlier(Table[max last date ])),Table[value])
    • kormosb's avatar
      kormosb
      Helper III
      6 years ago

      Unfortunately it doesn't work, as you can see below, but thanks for the idea.

      Do you have any other tip for it? amitchandak 

       

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    HI kormosb,

    You can try to use following calculate column formula, it can extract the last date and previous date to find out corresponding values:

     

    last date value =
VAR prevDate =
    CALCULATE (
        MAX ( 'Issue Navigator'[Latest refresh date] ),
        FILTER (
            'Issue Navigator',
            [Key] = EARLIER ( 'Issue Navigator'[Key] )
                && DATEVALUE ( [Latest refresh date] )
                    < DATEVALUE ( EARLIER ( 'Issue Navigator'[Latest refresh date] ) )
        )
    )
RETURN
    IF (
        'Issue Navigator'[Latest refresh date] = BLANK ()
            || DATEVALUE ( 'Issue Navigator'[Latest refresh date] )
                = DATEVALUE ( MAX ( 'Issue Navigator'[Latest refresh date] ) ),
        CALCULATE (
            MAX ( 'Issue Navigator'[Solving day (original)] ),
            FILTER (
                'Issue Navigator',
                [Key] = EARLIER ( 'Issue Navigator'[Key] )
                    && [Latest refresh date] = prevDate
            )
        ),
       'Issue Navigator'[Solving day (original)]
    )

     

    Regards,
    Xiaoxin Sheng

    • kormosb's avatar
      kormosb
      Helper III
      6 years ago

      Hi,

       

      Thanks for the solution, it's good, but I have faced some other issues with it.

      What I truly need can be seen in the below picture. So not the last date value that I really need, I need the value when the [solution] column was first changed to "Done". In this case, the "Solving day" was 2, since 2020.03.18 was the frist date that it was accepted as "done".

      I wonder if you could help me with this as well?

       

      Thanks in advance,

      Ben

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        HI kormosb,

        You can extract the variable and use in a new calculated column to compare with current date:

        Status =
VAR prevDate =
    CALCULATE (
        MAX ( 'Issue Navigator'[Latest refresh date] ),
        FILTER (
            'Issue Navigator',
            [Key] = EARLIER ( 'Issue Navigator'[Key] )
                && DATEVALUE ( [Latest refresh date] )
                    < DATEVALUE ( EARLIER ( 'Issue Navigator'[Latest refresh date] ) )
        )
    )
RETURN
    IF ( 'Issue Navigator'[Latest refresh date] >= prevDate, "Done" )

        Regards,

        Xiaoxin Sheng