difference between the dates

[Rfranca]

I have a data list according to the figure, with t3 coluas SITE, EVENT, DATE.
I need to identify the difference between the dates of the events according to the SITE, EVENT, DATE

the DIFF_DATE column makes the difference between dates considering only DATE
the DIFF_SITE column makes the difference between the dates considering only SITE,
the DIFF_EVENT column makes the difference between the dates considering only EVENT,

This is to identify the greatest difference between the dates.
The filters will be applied as required and the calculations should be applied.

How to make these difference calculations?

Thank you.

[ashaikh]

Can you please describe more regarding how do you want to see the output.

[Rfranca]

1. calculate DIFF_DATE (difference between the current date and the previous date)
2. calculate DIFF_SITE (difference between the current date and the previous date) considering only SITE,
3. calculate DIFF_EVENT (difference between the current date and the previous date) considering only SITE, EVENT
4. Calculate the highest value for the DIFF_DATE, DIFF_SITE, and DIFF_EVENT columns

ex.DIFF_DATE (orange color)
09/05/2017 (current date) - 03/05/2017 (previous date of the list, classified) = 6 days

ex.DIFF_SITE (yellow color)
04/07/2017 (current date) - 06/14/2017 (previous date SITE_01 of the list, sorted) = 20 days

ex.DIFF_EVENT (yellow color)
13/11/2017 (current date) - 08/29/2017 (previous date SITE_01-EVENT_01 from list, sorted) = 76 days

ex. calculate the highest DIFF_DATE = 55 days

ex. calculate the highestDIFF_SITE = 76days (SITE_01)

ex. calculate the highestDIFF_EVENT = 76days (SITE_01-EVENT_01)

thanks

[v-jiascu-msft · Accepted solution]

Hi @Rfranca,

Please try out these calculated columns.

Diff_Date =
VAR current_index = Table1[Index]
VAR previous_date =
    CALCULATE (
        VALUES ( Table1[Date] ),
        FILTER ( ALL ( 'Table1' ), Table1[Index] = current_index - 1 )
    )
RETURN
    DATEDIFF ( previous_date, [Date], DAY )

Diff_Site =
VAR current_index = [Index]
VAR previous_site =
    CALCULATE (
        VALUES ( Table1[Name_Site] ),
        FILTER ( ALL ( 'Table1' ), 'Table1'[Index] = current_index - 1 )
    )
VAR current_site = [Name_Site]
RETURN
    DATEDIFF (
        CALCULATE (
            MAX ( [Date] ),
            FILTER (
                'Table1',
                'Table1'[Name_Site] = current_site
                    && Table1[Index] < current_index
                    && current_site <> previous_site
            )
        ),
        [Date],
        DAY
    )

diff_event_site =
VAR current_index = [Index]
VAR previous_site =
    CALCULATE (
        VALUES ( Table1[Name_Site] ),
        FILTER ( ALL ( 'Table1' ), 'Table1'[Index] = current_index - 1 )
    )
VAR previous_event =
    CALCULATE (
        VALUES ( Table1[Name_event] ),
        FILTER ( ALL ( 'Table1' ), 'Table1'[Index] = current_index - 1 )
    )
VAR current_site = [Name_Site]
VAR current_event = [Name_Event]
RETURN
    DATEDIFF (
        CALCULATE (
            MAX ( [Date] ),
            FILTER (
                'Table1',
                'Table1'[Name_Site] = current_site
                    && Table1[Index] < current_index
                    && 'Table1'[Name_Event] = current_event
                    && ( current_site <> previous_site
                    || current_event <> previous_event )
            )
        ),
        [Date],
        DAY
    )

Best Regards,

Dale