DAX Measure to count if two date conditions are met

[BooRadley]

I have been utilizing these forms for a few months now, and it has been an amazing source of help to learn how to use Power BI.

I am currently struggling with coming up with DAX Measure that I would use to show a count of rows that match two date requirements.  So, for the row to be counted the row needs one column value to be greater than the day the count is being done for, and then it would need to check an additional column and see if the date in that column is less than the day the count is being done for.  If both are true, it is counted.  If either or both are false, it is not counted

For example, using the data set below, I am trying to write an expression that will show the number of invoices open on every day of the time period.  For this set it would be January 4th through January 14th.  To do this I need to count the number of rows where the Created-on Date is less than the date of the count and that the Closure date is greater than the date of the count.

For example, at the end of January 6th there would be 8 invoices open

I'm hoping to use this to look at how the number of invoices opened changed day by day.  Thank you in advance for any help or any ideas someone may have.

InvoiceNumber	Invoice Created On	Invoice Closure date	Material description
88912	1/4/2022	1/5/2022	Item 3
89025	1/4/2022	1/13/2022	Item 14
88792	1/4/2022	1/14/2022	Item 17
88767	1/4/2022	1/28/2022	Item 4
89201	1/5/2022	1/18/2022	Item 11
89341	1/5/2022	1/6/2022	Item 17
89487	1/5/2022	1/31/2022	Item 5
89399	1/5/2022	2/1/2022	Item 11
89694	1/6/2022	1/19/2022	Item 11
89689	1/6/2022	2/2/2022	Item 9
90060	1/7/2022	1/25/2022	Item 6
90294	1/7/2022	2/3/2022	Item 3
90269	1/7/2022	2/5/2022	Item 10
90079	1/7/2022	2/6/2022	Item 6
90345	1/8/2022	1/30/2022	Item 13
90847	1/10/2022	1/26/2022	Item 1
90753	1/10/2022	2/7/2022	Item 6
90782	1/10/2022	2/8/2022	Item 6
91275	1/11/2022	1/15/2022	Item 1
90903	1/11/2022	1/27/2022	Item 6
90885	1/11/2022	2/9/2022	Item 6
91272	1/11/2022	2/16/2022	Item 1
91619	1/12/2022	1/20/2022	Item 6
91373	1/12/2022	1/21/2022	Item 11
91580	1/12/2022	2/4/2022	Item 7
91759	1/12/2022	2/10/2022	Item 11
91310	1/12/2022	2/12/2022	Item 12
91583	1/12/2022	2/13/2022	Item 8
92353	1/13/2022	1/16/2022	Item 2
92170	1/13/2022	1/17/2022	Item 4
92290	1/13/2022	1/22/2022	Item 15
92351	1/13/2022	1/29/2022	Item 2
91861	1/13/2022	2/11/2022	Item 3
91993	1/13/2022	2/14/2022	Item 3
92203	1/13/2022	2/15/2022	Item 16
92363	1/14/2022	1/23/2022	Item 17

[kitgo2 · Accepted solution]

Calendar table needs to have an inactive relationship in order to work:

#Active =

VAR _Active = MAX ( 'Calendar'[Date] )

RETURN

SUMX (

Sheet1,

IF (

Sheet1[Invoice Created On] <= _Active

&& ( Sheet1[Invoice Closure Date] > _Active ),

1,

0

)

)

[BooRadley]

Thank you, the key thing I was struggling with making the date relationship as inactive from active.  For some reason, I was hung up on thinking that it was required to have the open date as a active relationship.  I very much appreciate the help you have provided and how quickly you responded.

[ManguilibeKAO]

Hi BooRadley,

Please, explain how, with the inout data you provided,  you count 8 open inoices at the end of January 6th.

Best regards.

[BooRadley]

So at the end of the 6th, 10 invoices have been opened but 2 have been closed. Invoice 8892 closed on the 5th and invoice 89341 closed on the 6th. 

I hope that clarifies what I meant. 

InvoiceNumber	Invoice Created On	Invoice Closure date	Material description
88912	1/4/2022	1/5/2022	Item 3
89025	1/4/2022	1/13/2022	Item 14
88792	1/4/2022	1/14/2022	Item 17
88767	1/4/2022	1/28/2022	Item 4
89201	1/5/2022	1/18/2022	Item 11
89341	1/5/2022	1/6/2022	Item 17
89487	1/5/2022	1/31/2022	Item 5
89399	1/5/2022	2/1/2022	Item 11
89694	1/6/2022	1/19/2022	Item 11
89689	1/6/2022	2/2/2022	Item 9