Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
PBI_newuser
Post Prodigy
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
) )
OrderCreated DateClosed Date
PO-12301-01-1902-14-19
PO-23401-01-1903-15-19
PO-34504-02-1904-30-19
PO-45604-02-1909-24-19
PO-56704-02-1907-22-19


Expected Output:

PBI_newuser_0-1623291418897.png

 

1 ACCEPTED SOLUTION
Jihwan_Kim
Super User
Super User

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

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.


Go to My LinkedIn Page


View solution in original post

4 REPLIES 4
Jihwan_Kim
Super User
Super User

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

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.


Go to My LinkedIn Page


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

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.


Go to My LinkedIn Page


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

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.