Count open items each day, 2 date columns

Question

Hi team,&nbsp;I have a date table, and a data table, the three relevant columns from the data table are:&nbsp;IDDate IssuedCompliance Date11/01/202010/01/202025/01/202014/01/2020313/01/202020/01/2020416/01/202022/01/2020&nbsp;I need 3 measures, 2 I've done, stuck on the third. My date table has an active relationship between Date and Date Issued, and an inactive relationship between Date and Compliance Date.&nbsp;For the first measure, items raised on a given day, it's just a distinctcount of the id&nbsp;For the second measure, items closed on a given day, it'sCALCULATE([Raised], USERELATIONSHIP('Calendar'[Date],'Data Table'[Compliance Date]))&nbsp;The third measure, I need to be able to show how many were open on any given day. So where Date Issued is &lt;= 'Calendar'[Date], and Compliance Date is &gt; 'Calendar'[Date] || Blank.&nbsp;&nbsp;Edit: Sorry I should add that I'm trying to avoid doing this in another table like I usually would, because the visual output for this measure will require using a slicer and/or legend for other columns in the data table

swikritee_p · Accepted Answer

Hickling&nbsp;, You can try a measure like one used in HR Analytics&nbsp;CALCULATE(COUNTx(FILTER(Employee,Employee[Start Date]&lt;=max('Date'[Date]) &amp;&amp; (ISBLANK(Employee[End Date]) || Employee[End Date]&gt;max('Date'[Date]))),(Employee[Employee Id ])),CROSSFILTER(Employee[Start Date],'Date'[Date],None))You can refer this bloghttps://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970

Forum Discussion

Count open items each day, 2 date columns

2 Replies

Related content

Total Count in Column

Count counting some items twice

Distinct count branches by item

Counting nulls

Column count duplicates

ID	Date Issued	Compliance Date
1	1/01/2020	10/01/2020
2	5/01/2020	14/01/2020
3	13/01/2020	20/01/2020
4	16/01/2020	22/01/2020