Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
kormosb
Helper III
Helper III

EARLIER function?

Hi Guys,

 

I am using EARLIER to get the expected results shown in the below picture ([last date value] red numbers).

I need the [solving day (original)] value to every row after 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".

 

earlier issue 3.JPG

 

Currently I am using this DAX formula:

last date value =
    VAR prevDate =
    CALCULATE (
    MAX ( 'Issue Navigator'[latest refresh date] );
    FILTER (
    'Issue Navigator';
    [Key] = EARLIER ( 'Issue Navigator'[Key] )
    && [latest refresh date]
    < EARLIER ( 'Issue Navigator'[Latest refresh date] )
    ))

VAR firstDoneDate =
CALCULATE(
    MIN ( 'Issue Navigator'[latest refresh date] );
FILTER (
    'Issue Navigator';
[Key] = EARLIER ( 'Issue Navigator'[Key] )
    && [Solution]="Done")
)


    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 && [Latest refresh date] = firstDoneDate
    )
    );
    'Issue Navigator'[Solving day (original)]
    )
 
Here you can find the sample files: Sample data 
 
Thanks in advance,
Benjamin
1 ACCEPTED SOLUTION
v-lid-msft
Community Support
Community Support

Hi @kormosb ,

 

We can use the following DAX formula to meet your requirement.

 

last date value =
VAR prevDate =
    CALCULATE (
        MIN ( 'Issue Navigator'[latest refresh date] ),
        FILTER (
            'Issue Navigator',
            [Key] = EARLIER ( 'Issue Navigator'[Key] )
                && 'Issue Navigator'[Solution] = "Done"
        )
    )
RETURN
    IF (
        'Issue Navigator'[Solution] = BLANK (),
        'Issue Navigator'[Solving day (original)],
        CALCULATE (
            MAX ( 'Issue Navigator'[Solving day (original)] ),
            FILTER (
                'Issue Navigator',
                [Key] = EARLIER ( 'Issue Navigator'[Key] )
                    && [Latest refresh date] = prevDate
            )
        )
    )

 

The result like this,

 

31.png


By the way, PBIX file as attached.


Best regards,

 

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

2 REPLIES 2
v-lid-msft
Community Support
Community Support

Hi @kormosb ,

 

We can use the following DAX formula to meet your requirement.

 

last date value =
VAR prevDate =
    CALCULATE (
        MIN ( 'Issue Navigator'[latest refresh date] ),
        FILTER (
            'Issue Navigator',
            [Key] = EARLIER ( 'Issue Navigator'[Key] )
                && 'Issue Navigator'[Solution] = "Done"
        )
    )
RETURN
    IF (
        'Issue Navigator'[Solution] = BLANK (),
        'Issue Navigator'[Solving day (original)],
        CALCULATE (
            MAX ( 'Issue Navigator'[Solving day (original)] ),
            FILTER (
                'Issue Navigator',
                [Key] = EARLIER ( 'Issue Navigator'[Key] )
                    && [Latest refresh date] = prevDate
            )
        )
    )

 

The result like this,

 

31.png


By the way, PBIX file as attached.


Best regards,

 

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Thanks, it works!:)

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.