Count the open orders prior to that date

[PBI_newuser]

Hi, I have a set of date with Created Date and Closed Date as below.

I would like to count the orders in Open state prior to that date. I used the below measures but it doesn't work.

Sample here.

Order Count = 
VAR minDate = MIN ('Table'[Created Date])
VAR maxDate = MAX ('Table'[Closed Date])
RETURN
CALCULATE (DISTINCTCOUNT ('Table'[Order]),
 FILTER (
 'Calendar',
 'Calendar'[Date] >= minDate 
 && 'Calendar'[Date] <= maxDate
 ) )

Order	Created Date	Closed Date
PO-123	01-01-19	02-14-19
PO-234	01-01-19	03-15-19
PO-345	04-02-19	04-30-19
PO-456	04-02-19	09-24-19
PO-567	04-02-19	07-22-19

Expected Output:

[Jihwan_Kim · Accepted solution]

Hi, @PBI_newuser 

Please try the below measure.

Order Count =
CALCULATE (
DISTINCTCOUNT ( 'Table'[Order] ),
FILTER (
'Table',
'Table'[Created Date] <= MAX ( 'Calendar'[Date] )
&& 'Table'[Closed Date] >= MIN ( 'Calendar'[Date] )
)
)

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM

[PBI_newuser]

Hi @Jihwan_Kim , how to calculate if the start date should be [Created Date] + 45days?

[Jihwan_Kim]

Hi, @PBI_newuser 

I am not sure if I understood your last question correctly.

Do you mean that you want to count the number of orders between start date and closed date?

If so, you can replace 'Table'[Created Date]  with 'Table'[Created Date] +45 in the measure.

Thanks.

[PBI_newuser]

Hi @Jihwan_Kim , yes, you are right! Thanks a lot! 

One more question, how to include this condition below in the calculation?

For example, the due date is 04/24/2020, and the month of Calendar date is 04/12/2020, then exclude it. If the due date is 04/24/2020, and the month of Calendar date is 05/1/2020, then include them. I would like to include only "Open" status in the calculation but the below measure is not working.

Open Flag = 
IF('Table'[Due Date]=BLANK(),"Open",
IF(OR(AND(MONTH('Table'[Due Date])=MONTH('Calendar'[Date]),
          YEAR('Table'[Due Date])=YEAR('Calendar'[Date])),
        'Calendar'[Date]<'Table'[Due Date]),
"Closed","Open"))