Calculation of open order on planning

[ieatwood]

Hi everyone,

I'm struggling with finding the right DAX code to get my open orders planned for each day that exist over time.

My first table (Productions) consists:

Production ID	Date Ordered	Date on Planning
FR100	10/10/2022	18/10/2022
NL101	21/10/2022	24/10/2022
IT102	22/10/2022	25/10/2022
FR103	23/10/2022	29/10/2022
DE104	26/10/2022	2/11/2022
NL105	28/10/2022	2/11/2022
BE106	1/11/2022	5/11/2022
FR107	2/11/2022	6/11/2022
FR108	3/11/2022	7/11/2022

I also have a basic calendar table related to Date Ordered and Date on Planning (secondary) to do some reporting on these dates.

However, what I want to accomplish is the following:

I want to create a measure to get the "Open Doors on Planning":

>> this should count all my (Production ID) where (Date Ordered) <= Date && (Date Planned) >= Date

• Date should be a continious timeframe of all Dates from a table (probably unlinked new table?)
• I think it should be done with a measure countrows but i struggle to relate this measure on a date table unrelated to my existing Productions table.
• Can anyone help me figuring out which measure will help me get the below "Open Doors on Planning" and how to fit it on an unrelated date table?

So it should result in:

Date	Open Doors on Planning	which doors?
8/10/2022	0	none
9/10/2022	0	none
10/10/2022	1	FR100
11/10/2022	1	FR100
12/10/2022	1	FR100
13/10/2022	1	FR100
14/10/2022	1	FR100
15/10/2022	1	FR100
16/10/2022	1	FR100
17/10/2022	1	FR100
18/10/2022	1	FR100
19/10/2022	0	none
20/10/2022	0	none
21/10/2022	1	NL101
22/10/2022	2	NL101, IT102
23/10/2022	3	NL101, IT102, FR103
24/10/2022	3	NL101, IT102, FR103
25/10/2022	2	IT102, FR103
26/10/2022	2	FR103, DE104
27/10/2022	2	FR103, DE104
28/10/2022	3	FR103, DE104, NL105
29/10/2022	3	FR103, DE104, NL105
30/10/2022	2	DE104, NL105
1/11/2022	3	DE104, NL105, BE106
2/11/2022	4	DE104, NL105, BE106, FR107
3/11/2022	3	BE106, FR107, FR108
4/11/2022	3	BE106, FR107, FR108
5/11/2022	3	BE106, FR107, FR108
6/11/2022	2	FR107, FR108
7/11/2022	1	FR108
8/11/2022	0	none

If any other questions please let me know.

Thanks a million!

Tim

[v-jayw-msft · Accepted solution]

Hi @ieatwood ,

Please check the measure:

Measure = CALCULATE(COUNT(Productions[Production ID]),FILTER(ALLSELECTED(Productions),Productions[Date Ordered]<=SELECTEDVALUE('Calendar'[Date])&&Productions[Date on Planning]>=SELECTEDVALUE('Calendar'[Date])))+0

Best Regards,

Jay

[amitchandak]

@ieatwood , Use HR blog approach, active employees. but use concatenatex in place of countx

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

Concatenatex: https://www.youtube.com/watch?v=du2HSEzng2E&list=PLPaNVDMhUXGaaqV92SBD5X2hk3TMNlHhb&index=43