Count date values across multiple columns in visual

[aallman]

I have a data set with 3 calculated columns that result in the deadlines for different steps of my process as well as 3 columns with the actual completion dates of each step. I need to create a visual that shows the trend over time of % of On-Time steps. (Example of desired visual using only one of the process steps is below)

So what I need to do is this:

if my data looks like this

RefID	Step1 Deadline	Step2 Deadline	Step3 Deadline	Step1 Actual	Step2 Actual	Step3 Actual
1	10/15/23	10/16/23	10/18/23	10/15/23	10/17/23	10/18/23
2	10/16/23	10/17/23	10/19/23	10/16/23	10/17/23	10/20/23
3	10/15/23	10/16/23	10/18/23	10/16/23	10/17/23	10/18/23
4	10/17/23	10/18/23		10/17/23	10/18/23

then I need to find a way to have a single date field as my X axis and create 2 measures: one that counts how many total checkins were required that day (deadlines) which in this example for the date 10/16/23 would be 3; and one that calculates the On Time % of check ins for each day which in this example for the date 10/16/23 would be 33%.

I already have columns that mark each check in as On Time or Late. 

I tried to add a date table that isn't linked to my data table in any way but it isn't counting things correctly and I can't figure out why. 

EDIT: I would also like to somehow to be able to have a table visual linked to the chart so that when the user clicks on one of the date's bars the table visual shows the lines of the original data table that are in that day. Not sure if this is possible though.

[Anonymous · Accepted solution]

Hi @aallman ,

I created a sample pbix file(see the attachment), please check if that is what you want.

1. Create a date dimension table(DO NOT create any relationship with the fact table)

2. Create two measures as below

Count of deadlines =
VAR _date =
    SELECTEDVALUE ( 'Date'[Date] )
RETURN
    CALCULATE (
        DISTINCTCOUNT ( 'Data'[RefID] ),
        FILTER (
            ALLSELECTED ( 'Data' ),
            'Data'[Step1 Deadline] = _date
                || 'Data'[Step2 Deadline] = _date
                || 'Data'[Step3 Deadline] = _date
        )
    )

On-Time % =
VAR _date =
    SELECTEDVALUE ( 'Date'[Date] )
VAR _actuals =
    CALCULATE (
        DISTINCTCOUNT ( 'Data'[RefID] ),
        FILTER (
            'Data',
            ( ( 'Data'[Step1 Deadline] = _date
                && 'Data'[Step1 Actual] <= 'Data'[Step1 Deadline] )
                || ( 'Data'[Step2 Deadline] = _date
                && 'Data'[Step2 Actual] <= 'Data'[Step2 Deadline] )
                || ( 'Data'[Step3 Deadline] = _date
                && 'Data'[Step3 Actual] <= 'Data'[Step3 Deadline] ) )
        )
    )
RETURN
    DIVIDE ( _actuals, [Count of deadlines] )

3. Create line chart

Best Regards