DateDiff using slicers

[Anonymous]

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. 

[Icey · Accepted solution]

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 · Accepted solution]

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.

[Anonymous]

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

[Icey · Accepted solution]

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.

[lbendlin]

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.