Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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
Can you please describe more regarding how do you want to see the output.
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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
96 | |
77 | |
66 | |
53 |
User | Count |
---|---|
144 | |
105 | |
102 | |
89 | |
63 |