Forum Discussion

Rfranca's avatar
Rfranca
Resolver IV
8 years ago
Solved

difference between the dates

  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 c...
Need Help
  • v-jiascu-msft's avatar
    v-jiascu-msft
    8 years ago

    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
    )

    difference_between_the_dates

     

    Best Regards,

    Dale

Rfranca's avatar
Rfranca
Resolver IV
8 years ago

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's avatar
v-jiascu-msft
Microsoft Employee
8 years ago

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
    )

difference_between_the_dates

 

Best Regards,

Dale