Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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:
Solved! Go to 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
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.
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.
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.
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"))
User | Count |
---|---|
94 | |
86 | |
78 | |
69 | |
63 |
User | Count |
---|---|
114 | |
99 | |
97 | |
65 | |
59 |