Count cases for a period, which may completed even in another period

[addicted87]

hi to all of you,

yet I have another issue with creating a measure, which counts the completed number of cases per supplier. So far it is not a problem. BUT... it is possible, that the case will be completed on a following quarter, compared to the selected one.

example for one supplier:

supplier	caseid	quarter of order	quarter of completion
1	10	1	1
1	11	1	2
1	12	2	2
1	13	3	4

So if I select

quarter = 1, I expect the measure value = 1

quarter = 2, I expect the measure value = 2

quarter = 3, I expect the measure value = 0

quarter = 4, I expect thr measure value = 1

Alright, there is always one quarter selected, so I could use the quarter of completion for a result table. But I need the number of cases, bases on quarter of order as well in the result table and I can only filter the quarter either on order quarter or completion quarter.

the result table for quarter 1 should looks like this:

supplier	# of cases	# of completed cases
1	2	1
2	n	m
...	...	...

Hope someone can help me;-)

THX

[amitchandak]

@addicted87 , I did not get the logic , you are using

[addicted87]

hey @amitchandak ,

lets say we have an entry for supplier 1:

supplier 1 | caseid | order quarter | completion quarter |

1 | ABC | 1 | 3

1 | ABD | 3 | 4

If the user selects quarter 3 (based on order date) and looks at the result table, he will get this list:

Supplier | # of new orders | # of completed orders

1 | 1 | 0 --> but this is not correct. Indeed, there is just one new order in Q3 (ABD) --> correct, but there is also a completed order (ABC) in Q3, so I'd like to display a 1 in column [# of completed orders] (measure).

The problem is, that the order quarters and completion quarters aren't always the same per record, so if I filter one date (order vs. complete) the data will also get filtered and records will be lost in calculation.

I hope, this one is more understandable than in my first post.

Thx

[AllisonKennedy · Accepted solution]

@addicted87 have you looked at the link I posted? Do you have a DimDate table? https://excelwithallison.blogspot.com/2020/04/dimdate-what-why-and-how.html
You will need one for this, with active relationship to OrderDate and Inactive relationship to CompletedDate

then put DimDate[Quarter] in the slicer and create 2 measures for [Orders] = Count(Sales[CaseID])
and [Completed Orders] = CALCULATE(Count(Sales[CaseID]), USERELATIONSHIP(Sales[CompletedDate], DimDate[Date])

[AllisonKennedy]

Have you tried the USERELATIONSHIP function for this? https://blog.enterprisedna.co/working-with-multiple-dates-in-power-bi/