Forum Discussion

SvenVa's avatar
SvenVa
Regular Visitor
7 years ago
Solved

Neem some help with EARLIER function

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?

Need Help
  • v-frfei-msft's avatar
    v-frfei-msft
    7 years ago

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

     

     

     

2 Replies

  • v-frfei-msft's avatar
    v-frfei-msft
    Community Support
    7 years ago

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

     

     

     

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    7 years ago

    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.