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.
Hi,
I have a dataset (much larger and not filtered however) which looks like the following:
Event Date | ID |
26/07/2016 | 7076341 |
26/07/2016 | 7055292 |
2/08/2016 | 7055292 |
20/09/2016 | 7076341 |
25/10/2016 | 7076341 |
22/11/2016 | 7076341 |
16/12/2016 | 7076341 |
23/12/2016 | 7311257 |
I need to calculate the days between the first and last date for each unique ID. The formula might also need to check if an ID has two dates or not.
I've explored a few ideas but am not that confident in DAX.
Thanks in advance for your help,
Solved! Go to Solution.
HI @redbrumby
This calculated table may give you want you want. I have attached a simple PBIX file
Table = SUMMARIZECOLUMNS( 'Table1'[ID] , "Min Date" , MIN('Table1'[Event Date]) , "Max Date" , MAX('Table1'[Event Date]) , "Date Diff" , DATEDIFF( MIN('Table1'[Event Date]), MAX('Table1'[Event Date]),DAY) )
Hi,
i dragged ID to the rows labels of the Table visual and wrote the following measure
Diff = 1*(MAX(Data[Event Date])-MIN(Data[Event Date]))
Hope this helps.
HI @redbrumby
This calculated table may give you want you want. I have attached a simple PBIX file
Table = SUMMARIZECOLUMNS( 'Table1'[ID] , "Min Date" , MIN('Table1'[Event Date]) , "Max Date" , MAX('Table1'[Event Date]) , "Date Diff" , DATEDIFF( MIN('Table1'[Event Date]), MAX('Table1'[Event Date]),DAY) )
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 |
---|---|
96 | |
95 | |
83 | |
70 | |
66 |
User | Count |
---|---|
118 | |
106 | |
93 | |
79 | |
72 |