Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

On Time calculation with comparison between two dates and filter on one date

Dear BI pros,

 

I hope you can help me with the following:

I am trying to count the processing steps that were performed on time for any given period.

But for some reason I am having problems with the DAX function:

 


on time = CALCULATE(DISTINCTCOUNT(Processing Steps[Processing Steps No]),

Processing Steps[Processing Steps Status]="Finished",

USERELATIONSHIP(Processing Steps[Target Enddate],Date[Date]),

Processing Steps[Actual Enddate]<=Processing Steps[Target Enddate]))

 

DAX does not accept the last part: The actuals enddate should be before the Target Enddate.

I receive the error: The expression contains multiple columns, but only a single column can be used in a true/false expression, which serves as a table filter expression.

I tried wrapping the FILTER function around it, but it didn't help.

Here the set up:
Tables:

DIM_Date: active relationship to start date in Processing Steps table and inactive relationship to Target Enddate.

Fact_Processing Steps: Processing Steps Nr, Target Enddate, Status, Actual Enddate, Start Date

Because there were blanks in the Actual Enddate , I filled these with the date 2099.12.31, hoping that would help...

In the end the enduser should be able to chose any date and the visual should show the amount of closed processing steps that had the Target Enddate in the chosen period (thus the inactive relationship), the Status on 'Finished' and the Actual Enddate before or equal to the chosen (Target End-)date.

What am I missing?

Thanks for your help!

Smeek

2 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    5 years ago

    Anonymous - If this is a measure, you need to wrap column references in an aggregator so maybe try. @ me in replies

     

    on time = CALCULATE(DISTINCTCOUNT(Processing Steps[Processing Steps No]),

Processing Steps[Processing Steps Status]="Finished",

USERELATIONSHIP(Processing Steps[Target Enddate],Date[Date]),

MAX(Processing Steps[Actual Enddate])<=MAX(Processing Steps[Target Enddate])))

    If not, not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

    Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

    The most important parts are:
    1. Sample data as text, use the table tool in the editing bar
    2. Expected output from sample data
    3. Explanation in words of how to get from 1. to 2.

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Hi Greg,

       

      thanks for your answer.

      Yes, it is a measure I am trying to calculate.

      I tried wrapping the date comparision with MAX, MIN, Filter...but it doesn't work. The message: Only a column reference is accepted as argument by the function 'MAX'.

       

      Both date columns are formated as dates, although the 'Actual Enddate' is a calculated column pulling the dates from another table via RELATED.

      I will try and see if I can setup a sample database.

       

      Thanks