EARLIER function

[kormosb]

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

[v-shex-msft · Accepted solution]

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

[v-shex-msft · Accepted solution]

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]

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

[v-shex-msft · Accepted solution]

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

[amitchandak]

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]

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

Do you have any other tip for it? @amitchandak 

[amitchandak]

@kormosb 

Can you share sample data and sample output.

[kormosb]

Yes, here you are.

sample 

[az38]

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