Forum Discussion
DateDiff using slicers
Hello All,
I have a column of Task Names and a column of task dates. I would like to create a report that allows users to select two different tasks and determine the number of days between those two task dates.
Column A = Task Name
Column B = Task Date
I would need slicers to allow users to pick two tasks (presumably two separate slicers, and thinking they would be from two separate tables.
Desired Resulting Table
Task 1 Name , Task 2 Name , Date Difference Header
Task 1 Date , Task 2 Date , Date Difference in Days
I've tried several approaches so far and haven't been able to figure this out. Any help would be appreciated.
Hi Anonymous ,
Please check:
1. Enter data to create ColumnHeader1 table. The order is the count of task name +1.
2. Create ColumnHeader2 table.
ColumnHeader2 = UNION ( ADDCOLUMNS ( VALUES ( 'Table'[Task Name] ), "Order", CONVERT ( RIGHT ( [Task Name], SEARCH ( " ", [Task Name] ) - 3 ), INTEGER ) ), ColumnHeader1 )3. Create a slicer table.
Task Name Slicer = VALUES('Table'[Task Name])4. Create measures.
Date Difference in Days = VAR T1 = MIN ( 'Task Name Slicer'[Task Name] ) VAR T1_Date = CALCULATE ( MAX ( 'Table'[Task Date] ), 'Table'[Task Name] = T1 ) VAR T2 = MAX ( 'Task Name Slicer'[Task Name] ) VAR T2_Date = CALCULATE ( MAX ( 'Table'[Task Date] ), 'Table'[Task Name] = T2 ) RETURN DATEDIFF ( T1_Date, T2_Date, DAY )Value Measure = VAR Task_ = MAX ( 'ColumnHeader2'[Task Name] ) RETURN IF ( Task_ = "Date Difference in Days", [Date Difference in Days], CONVERT ( CALCULATE ( MAX ( 'Table'[Task Date] ), 'Table'[Task Name] = Task_ ), STRING ) )Measure = IF ( MAX ( 'ColumnHeader2'[Task Name] ) = "Date Difference in Days", 1, IF ( MAX ( 'ColumnHeader2'[Task Name] ) IN VALUES ( 'Task Name Slicer'[Task Name] ), 1 ) )5. Create a Matrix visual.
6. Then, you will get this:
BTW, .pbix file attached.
Best Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi Anonymous ,
You can create your ColumnHeader1 table like this:
ColumnHeader1 = ADDCOLUMNS ( DATATABLE ( "ColumnHeader", STRING, { { "Date Difference in Days" } } ), "Order", DISTINCTCOUNT ( 'Table'[Task Name] ) + 1 )Best Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
4 Replies
Yes, you need two independent tables (you could import the table once and then in Power Query create a reference)
Assign the tasks to their slicers, make each slicer single selection and then create a measure somewhere that says
diff = selectedvalue(table1[date])-selectedvalue(table2[date])
or use DATEDIFF() if you must.
Hi Anonymous ,
Please check:
1. Enter data to create ColumnHeader1 table. The order is the count of task name +1.
2. Create ColumnHeader2 table.
ColumnHeader2 = UNION ( ADDCOLUMNS ( VALUES ( 'Table'[Task Name] ), "Order", CONVERT ( RIGHT ( [Task Name], SEARCH ( " ", [Task Name] ) - 3 ), INTEGER ) ), ColumnHeader1 )3. Create a slicer table.
Task Name Slicer = VALUES('Table'[Task Name])4. Create measures.
Date Difference in Days = VAR T1 = MIN ( 'Task Name Slicer'[Task Name] ) VAR T1_Date = CALCULATE ( MAX ( 'Table'[Task Date] ), 'Table'[Task Name] = T1 ) VAR T2 = MAX ( 'Task Name Slicer'[Task Name] ) VAR T2_Date = CALCULATE ( MAX ( 'Table'[Task Date] ), 'Table'[Task Name] = T2 ) RETURN DATEDIFF ( T1_Date, T2_Date, DAY )Value Measure = VAR Task_ = MAX ( 'ColumnHeader2'[Task Name] ) RETURN IF ( Task_ = "Date Difference in Days", [Date Difference in Days], CONVERT ( CALCULATE ( MAX ( 'Table'[Task Date] ), 'Table'[Task Name] = Task_ ), STRING ) )Measure = IF ( MAX ( 'ColumnHeader2'[Task Name] ) = "Date Difference in Days", 1, IF ( MAX ( 'ColumnHeader2'[Task Name] ) IN VALUES ( 'Task Name Slicer'[Task Name] ), 1 ) )5. Create a Matrix visual.
6. Then, you will get this:
BTW, .pbix file attached.
Best Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@Icey ,
Thank you for the incredibly detailed response, which looks like it will solve my issue beautifully. The only challenge I have in getting started is that my # of tasks may change. You mentioned needing to set ColumnHeader1 Table to a value of Count Tasks +1. If this is dynamic, how would I go about this?
FYI I have an append process which is adding tasks from another source and is why this may be dynamic.
Thank you again and looking forward to your response.
Hi Anonymous ,
You can create your ColumnHeader1 table like this:
ColumnHeader1 = ADDCOLUMNS ( DATATABLE ( "ColumnHeader", STRING, { { "Date Difference in Days" } } ), "Order", DISTINCTCOUNT ( 'Table'[Task Name] ) + 1 )Best Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
