Dates between two dates of another table

[mwadhwani]

Hello Experts,

I have  two tables:
TableA (Starttime,EndTime)
TableB(EndTime,F_Value)

I need count of F_Value between StartTime and EndTime of TableA from TableB(EndTime)

Any help would be highly appreciated.

[Ashish_Mathur]

Hi,

Share a dataset and show the expected result.

[mwadhwani]

Hello @Ashish_Mathur,

Please find the below dataset:

https://www.dropbox.com/s/qmh248ioh7uh1u9/Dataset.xlsx?dl=0

Dataset has 3 tabs TableB,TableD,OutputStructure.

Note: I achieved above output using SQL.Below is the sql for reference:

SELECT
COUNT(D.OperatingValue),
C.StartTime,
C.EndTime
FROM C,
D
WHERE D.OperatingValue <= 5
AND D.ProcessTime BETWEEN C.StartTime AND C.EndTime
GROUP BY C.StartTime,
C.EndTime

Thanks

[v-qiuyu-msft · Accepted solution]

Hi @mwadhwani,

You can create a measure below: 

Measure = CALCULATE(COUNT('TableD'[OperatingValue]),FILTER(ALL('TableD'),'TableD'[OperatingValue]<=5 && TableD[ProcessTime] >=MAX('TableB'[StartTime]) && 'TableD'[ProcessTime]<=MAX(TableB[End Time])))

Then drag a table visual to the report, please Start Time, End Time and measure. You can download the attached pbix file to have a look. 

Best Regards,
Qiuyn Yu