Combined Count Based on Different Dates in Different Tables

[jusTodd]

I am looking to get a count of items in TableA based on the date in TableA and a total count of items in TableA and TableB, based a different date in TableB.

Looking for a little feedback, since I work in silo and am only about 6 months in with PowerBI.  I would like to know if this can be achieved in DAX, or if the Append Query I created is really for the best.  It certainly seems the easiest solution.

(EDIT - The biggest problem with the current solution is filtering based on another column that identifies Scheduled Maintenance.  At this time, I have to add the column to the filter 3x on one page ... am hoping a little proper DAX can solve that issue).

The two tables, 'OpenWOs' and 'ClosedWOs' of which I need a count of

1) Open work orders by the month in which they were created

2) Closed work orders by the month in which they were closed

3) Created work orders by the month in which they were created

The tables are joined to a calendar table to facilitate a rolling 12 months in the visualizations.

OpenWOs is joined by [Date Created] and ClosedWOs is joined by [Date Completed], which gives me a proper count of exactly when the Opens were created and Closed were completed.

The problem lies with getting the count for all  WOs created in the month they were created, since I have ClosedWOs joined by [Date Completed].  Obviously, it won't work if I join by [Date Created] either.

Consequently, I created an Append Query to pull the Created Dates and Closed Dates from their respective tables into one to overcome this challenge, and because I simply could not figure out proper DAX for the situation.  That table 'CreatedWOs' is also joined to the calendar table and everything gives me the exact numbers that I expect.

The following image shows the results I am after, which show Created WOs in the month they were created, Open WOs from they were created, and Closed WOs in the month they were closed.

The following are sample data and a snapshot of the model. 

Open WOs

WO ID	Status	Date Created
1690	Open	7/19/2022
2002	Open	9/1/2022
2003	Open	9/1/2022
2068	Open	9/21/2022
2069	Open	9/21/2022
2070	Open	9/21/2022
2071	Open	9/21/2022
2072	Open	9/21/2022
2073	Open	9/21/2022

Closed WOs

WO ID	Status	Date Created	Date Complete
92	Closed, Completed	9/21/2021	1/5/2023
451	Closed, Incomplete	7/19/2022	1/24/2023
750	Closed, Incomplete	9/1/2022	1/25/2023
786	Closed, Completed	2/20/2022	1/6/2023
874	Closed, Completed	3/3/2022	1/19/2023
994	Closed, Incomplete	9/21/2022	1/24/2023
1276	Closed, Completed	5/18/2022	1/3/2023
1482	Closed, Completed	6/15/2022	1/25/2023

[amitchandak]

@jusTodd , Closed WOs contain created as well as the closed date that should work for both open and close, You should bring a table having Created date, closed date, and null if open

or append the table Open WOs & Closed WOs in power query and then this approach will work

Power BI: HR Analytics - Employees as on Date : https://youtu.be/e6Y-l_JtCq4
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

[jusTodd]

Thanks for the response.  That is exactly what I have done so far.  Getting past having the filter from each table for the same field seems the biggest challenge just now.