Neem some help with EARLIER function

[SvenVa]

Hi all,

I'm working on a visual to see the decrease in the gap between renewal times of education signups. 

An account can have signups for multiple education. I want to see the difference in days between signups of the same education type for the same account.

This is some example data:

This is the function I am trying to use:

DaysBetweenEduSignUps = DATEDIFF(EducationAgreements[Start date];filter(EducationAgreements;EARLIER(EducationAgreements[Start date]));DAY)

I'm pretty sure this is not the complete formula and it also gives this error: The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value.

I'm struggeling with this function and also how I should like it to the same education type and account. 

Can anyone help me out?

[Ashish_Mathur]

Hi,

Write this calculated column formula

Diff = [End Date]-[Start Date]

To your visual, drag Account Name and Education fields.  Write this measure

Total diff = SUM(Data[Diff])

Hope this helps.

[v-frfei-msft · Accepted solution]

Hi @SvenVa ,

One sample for your reference. Please check the following steps as below.

1. Create an index column by education column.

index = 
RANKX (
    FILTER ( Table1, Table1[Education] = EARLIER ( Table1[Education] ) ),
    'Table1'[Start date],
    ,
    DESC,
    DENSE
)

2. Create the target column we need.

Days = 
VAR index = Table1[index] - 1
VAR eardate =
    CALCULATE (
        MAX ( 'Table1'[Start date] ),
        FILTER (
            Table1,
            Table1[index] = index
                && 'Table1'[Education] = EARLIER ( 'Table1'[Education] )
        )
    )
RETURN
    IF (
        ISBLANK ( eardate ),
        BLANK (),
        DATEDIFF ( Table1[Start date], eardate, DAY )
    )