Count based on 2 columns in table

[prinsniels]

Hi,

Hope someone can help

I have 2 tables;

1. Table1 holding dates

2. Table2 holding orders

I want to visualize the amount of open orders through time. Meaning,

Table 2 holds three columns, OrderNr, StartDate and EndDate.

I want to get the amount of records from table 2 where StartDate <= date in table1 and EndDate <= date in table1. So that I can visualize the open workload at any give time interval.

Please help.

[Greg_Deckler · Accepted solution]

Create a measure (or calculated column in Dates table): like this:

OrderCount = CALCULATE(COUNT(Orders[OrderNo]),FILTER(RELATEDTABLE(Orders),Orders[StartDate]<='Calendar'[Date] && Orders[EndDate]>='Calendar'[Date]))