Forum Discussion
How to calculate days difference based on slicer selection
Hi, I have a table below. I want to calculate the mean time between jobs based on serial number and task codes selected. For example, when I do not select any Task Code, the table will sort Serial Number and Restoration Date in ascending order, then calculate the days difference between current Notified Date and previous job's Restoration Date. If I select Task Code = T113, the data will rearrange based on Serial Number and Notified Date then calculate the days difference.
How to create such measure to calculate the days difference based on task codes selected?
Table:
| Job Number | Serial Number | Task Code | Notified Date | Restoration Date |
| Job-1 | SN-1 | T110 | 01-Jan | 05-Jan |
| Job-2 | SN-1 | T112 | 05-Jan | 07-Jan |
| Job-3 | SN-1 | T113 | 11-Jan | 12-Jan |
| Job-4 | SN-1 | T113 | 20-Jan | 03-Feb |
| Job-5 | SN-1 | T112 | 22-Jan | 03-Mar |
| Job-6 | SN-1 | T110 | 27-Jan | 28-Jan |
| Job-7 | SN-2 | T112 | 01-Feb | 04-Feb |
| Job-8 | SN-2 | T112 | 14-Feb | 04-Mar |
Expected Output when Task Code is all selected:
| Job Number | Serial Number | Task Code | Notified Date | Restoration Date | Prev Restoration Date | Date Diff = Notified Date - Prev Restoration Date |
| Job-1 | SN-1 | T110 | 01-Jan | 05-Jan | ||
| Job-2 | SN-1 | T112 | 05-Jan | 07-Jan | 05-Jan | 0 |
| Job-3 | SN-1 | T113 | 11-Jan | 12-Jan | 07-Jan | 4 |
| Job-4 | SN-1 | T110 | 27-Jan | 28-Jan | 12-Jan | 15 |
| Job-5 | SN-1 | T113 | 20-Jan | 03-Feb | 28-Jan | -8 |
| Job-6 | SN-1 | T112 | 01-Feb | 04-Feb | 03-Feb | -2 |
| Job-7 | SN-2 | T112 | 22-Jan | 03-Mar | ||
| Job-8 | SN-2 | T112 | 14-Feb | 04-Mar | 03-Mar | -17 |
Expected Output when Task Code=T113 is selected:
| Job Number | Serial Number | Task Code | Notified Date | Restoration Date | Prev Restoration Date | Date Diff = Notified Date - Prev Restoration Date |
| Job-3 | SN-1 | T113 | 11-Jan | 12-Jan | ||
| Job-4 | SN-1 | T113 | 20-Jan | 03-Feb | 12-Jan | 8 |
3 Replies
- Ahmedx
Super User
Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.
https://1drv.ms/u/s!AiUZ0Ws7G26Rhk2uQYUTV9rgFi4q?e=wUR95aHi Ahmedx , how to calculate the day difference between the current Notified Date and Restoration Date of last job? I need to sort it by serial number and restoration date then calculate the dates difference like the example below?
Job Number Serial Number Task Code Notified Date Restoration Date Prev Restoration Date Date Diff = Notified Date - Prev Restoration Date Job-3 SN-1 T113 11-Jan 12-Jan Job-4 SN-1 T113 20-Jan 03-Feb 12-Jan 8
- Ahmedx
Day = VAR _MinDate = CALCULATE ( MIN ( 'Table'[Restoration Date] ), OFFSET (-1,SUMMARIZE (ALLSELECTED ( 'Table' ), 'Table'[Job Number], 'Table'[Serial Number], 'Table'[Task Code], 'Table'[Notified Date], 'Table'[Restoration Date] ), ORDERBY ( 'Table'[Job Number] ), KEEP, PARTITIONBY ( 'Table'[Serial Number] ) ) ) VAR _Date = IF ( NOT ISBLANK ( _MinDate ), _MinDate, MAX ( 'Table'[Notified Date] ) ) RETURN DATEDIFF ( MAX ( 'Table'[Notified Date] ), _Date, DAY )
