Count Rows Falling between two dates ( from disconnected table )

[sagar512]

Hi,

I am having two disconnected tables ( Task and Ticket).

On Task Table I need to count tickets (from the Ticket table) that fall between the task created date and Created +90 date.

In Excel, I used the below formula to get the expected output.

how can calculate the same in dax?

Task Table
Task Number	Created	Created +90
Task 1	1-Jan-21	1-Apr-21
Task 2	1-May-21	30-Jul-21
Task 3	1-Sep-21	30-Nov-21
Task 4	1-Jun-21	30-Aug-21
Task 5	1-Dec-21	1-Mar-22

Ticket Table
Ticket Number	Created
Ticket 1	7-Jan-21
Ticket 2	2-Feb-21
Ticket 3	2-Feb-21
Ticket 4	6-Jun-21
Ticket 5	10-Jun-21
Ticket 6	19-Aug-21
Ticket 7	9-Sep-21
Ticket 8	13-Jun-21
Ticket 9	3-Mar-21
Ticket 10	9-Sep-21

[smpa01 · Accepted solution]

Resolved here 

[Jihwan_Kim]

Hi,

Please check the below picture and the attached pbix file.

Expected Output: =
IF (
HASONEVALUE ( Task[Task Number] ),
COALESCE (
CALCULATE (
COUNTROWS ( Ticket ),
Ticket[Created] >= MAX ( Task[Created] )
&& Ticket[Created] <= MAX ( Task[Created +90] )
),
0
)
)

[sagar512]

thanks, but the problem with the above solution is I need to always keep the lowest level (Task number) into the context.

my end goal is to build some chart that aggregates to month level etc, but calculation should always stay at task number level and then aggregate.

I think the column was the best fit in this scenario but unfortunately, I can't use it due to direct query. (cant use calculate and x functions)  😞

[amitchandak]

@sagar512 , A new column in table 1

countx(filter(ticket, ticket[created]>= task[Created] && ticket[created]<= task[Created+90]) ,ticket[Ticket Number] )

the measure would be very similar to what we have here, but need to ticket in place of date

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

[sagar512]

Hi,

Thanks for the response;

but since these are two different tables I don't get the task created date under the filter function