Forum Discussion
Advocate IVCombined Count Based on Different Dates in Different Tables
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 |
2 Replies
- amitchandak
Super User
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-trend/ba-p/882970- 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.
