Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Need help in finding date from a daterange

I have 3 columns which I ma taking from 2 tables. 

Start date and end date from contract table

reqquestdatetime from workorder table.

I need count of workorders if my requestdatetime is lying in the range of startdate and enddate

I used - 

COUNT WO = CALCULATE(COUNT(WO[WorkOrder_IncidentNumber]),DATESBETWEEN(WO[WorkOrder_RequestDateTime],Contract[StartDate],Contract[EndDate]))
but giving me error "A single value for column 'StartDate' in table 'Contract' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result."
How can I use aggregate function in dates
Need Help

3 Replies

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

    Anonymous  try this

     

    _count =
CALCULATE (
    COUNT ( WO[WO] ),
    DATESBETWEEN ( WO[Date], MAX ( Contract[Start] ), MAX ( Contract[End] ) )
)
  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi Anonymous ，

    It seems that you are creating a measure and you can refer to user smpa01 's suggestion to update your measure formula. Or you can refer to the solution in the posts below to correct your formula and check later to see if you get the correct value...

    1. Create a calculated column replace with original measure 

    single value for column error

    2. Use the aggregation

    A single value for column 'Open_Date_Time__c' in table 'Problem' cannot be determined

    If the above methods do not help you to get the desired result, please provide some sample data of the Contract and WO tables and your final desired result with specific examples and calculation logic. Also, is there any relationship created between these two tables? Thank you.

    Best Regards