Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Power BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.

Reply
aallman
Helper I
Helper I

Count date values across multiple columns in visual

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)

aallman_0-1701204969108.png

 

So what I need to do is this:

if my data looks like this

RefIDStep1 DeadlineStep2 DeadlineStep3 DeadlineStep1 ActualStep2 ActualStep3 Actual
110/15/2310/16/2310/18/2310/15/2310/17/2310/18/23
210/16/2310/17/2310/19/2310/16/2310/17/2310/20/23
310/15/2310/16/2310/18/2310/16/2310/17/2310/18/23
410/17/2310/18/23 10/17/2310/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.

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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

vyiruanmsft_0-1701336439560.png

Best Regards

View solution in original post

1 REPLY 1
Anonymous
Not applicable

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

vyiruanmsft_0-1701336439560.png

Best Regards

Helpful resources

Announcements
June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.