How to check if date falls between two dates, and if so, add values in a column

[Hundoa]

Hello all, 

I've spent some time trying to come up with some logic, however I can't seem to get anywhere with it.... hopefully one of you can help. 

Below is a sample table, which shows the date that a piece of work was "received" and the date that it was completed ("Closed"). Each 'Case ref' is given a value ('Bulk Count') of 1 in this example, however in the real dataset this could be any number. 

[CASES TABLE]

If the date in the below table is the => than the Received date and < Closed date, then I want to sum all the row values in the Bulk Count column, where that condition is met.

[DATE TABLE]

So, the final table should look something like this;

Sorry if that doesn't make any sense, my brain is a little fried. Please ask questions if you need any clarification. 

Thanks in advance. 

[V-lianl-msft]

Don't @Hundoa,

Try to create a measure like this:

Measure = CALCULATE(SUMX(FILTER(ALL('Table'),'Table'[Received]<=MAX('Table 2'[Date])&&'Table'[Closed]>=MAX('Table 2'[Date])),'Table'[Bulk count]))

Best regards
Liang
If this post helps, then consider Accept it as the solution to help other members find it faster.

[amitchandak]

@Hundoa , refer my HR blog on similar type of data.

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

video -https://www.youtube.com/watch?v=e6Y-l_JtCq4