Forum Discussion

aallman's avatar
aallman
Helper II
2 years ago
Solved

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 visua...
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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

Anonymous's avatar
Anonymous
Not applicable
2 years ago

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