Count open items each day, 2 date columns

[Hickling]

Hi team,

I have a date table, and a data table, the three relevant columns from the data table are:

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

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.

For the first measure, items raised on a given day, it's just a distinctcount of the id

For the second measure, items closed on a given day, it's

CALCULATE([Raised], USERELATIONSHIP('Calendar'[Date],'Data Table'[Compliance Date]))

The third measure, I need to be able to show how many were open on any given day. So where Date Issued is <= 'Calendar'[Date], and Compliance Date is > 'Calendar'[Date] || Blank. 

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 solution]

@Hickling , You can try a measure like one used in HR Analytics 

CALCULATE(COUNTx(FILTER(Employee,Employee[Start Date]<=max('Date'[Date]) && (ISBLANK(Employee[End Date]) || Employee[End Date]>max('Date'[Date]))),(Employee[Employee Id ])),CROSSFILTER(Employee[Start Date],'Date'[Date],None))

You can refer this blog

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

[Hickling]

This is perfect, thank you!