cancel
Showing results for
Did you mean:

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Post Prodigy

Count the open orders prior to that date

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:

1 ACCEPTED SOLUTION
Super User

Hi, @PBI_newuser

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.

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

4 REPLIES 4
Super User

Hi, @PBI_newuser

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.

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

Post Prodigy

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

Super User

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.

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

Post Prodigy

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"))

Announcements

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors