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

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