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

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

3 Replies

  • ashaikh's avatar
    ashaikh
    Helper III
    8 years ago

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

    • 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