Need help in finding date from a daterange

[niharikaavasthi]

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

[v-yiruan-msft]

Hi @niharikaavasthi ，

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

[Ashish_Mathur]

Hi,

Share some data and show the expected result.

[smpa01]

@niharikaavasthi  try this

_count =
CALCULATE (
    COUNT ( WO[WO] ),
    DATESBETWEEN ( WO[Date], MAX ( Contract[Start] ), MAX ( Contract[End] ) )
)