Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
I am trying to show counts of tickets opened and closed during the last full week prior to the date the report is refreshed. I'm also wanting to show an overall count of open tickets that were created before the last full week.
Here is some sample data:
*Note any row that shows something other than closed in the current_status field is considered open
Fact Table | ||||||
id | parent_id | create_date | closed_date | current_status | market | Open/Closed (Calc Column) |
880664 | 1/1/2023 | 2/1/2023 | Closed | West 1 | Closed | |
880665 | 880664 | 1/1/2023 | 2/1/2023 | Closed | West 1 | Closed |
880666 | 880664 | 2/2/2023 | 3/22/2024 | Closed | West 2 | Closed |
880677 | 880664 | 3/20/2024 | 3/22/2024 | Closed | West 2 | Closed |
880678 | 880664 | 3/21/2024 | 3/27/2024 | Closed | West 3 | Closed |
880679 | 880664 | 3/5/2024 | 3/27/2024 | Closed | West 3 | Closed |
880660 | 1/1/2023 | Alarm Clear Received | East 1 | Open | ||
880661 | 880660 | 5/1/2023 | Investigating | East 1 | Open | |
880662 | 880660 | 3/22/2024 | Alarm Clear Received | East 2 | Open | |
880668 | 880660 | 3/24/2024 | Investigating | East 2 | Open | |
880669 | 880660 | 3/5/2024 | Alarm Clear Received | East 3 | Open | |
880670 | 880660 | 3/27/2024 | Alarm Clear Received | East 3 | Open |
Date Table | |||||
Date | Year | Quarter | Month | Week | Weekday |
1/1/2024 | 2024 | 1 | 1 | 1 | Monday |
This is how I want to display the data (in one table preferably):
Filtered for: Week 12 (March 17-23, 2024) - Filter would always be for the most recent full week. So this particular sample would display counts as of week 12 when pulled on March 27th.
Created Last Week | Created Last Week | Closed Last Week | Closed Last Week | Open Tickets Older than Week 12 | Open Tickets Older than Week 12 | |
Market | Distinct Count of parent_id | Distinct Count of id | Distinct Count of parent_id | Distinct Count of id | Distinct Count of parent_id | Distinct Count of id |
West 1 | ||||||
West 2 | ||||||
West 3 | ||||||
East 1 | ||||||
East 2 | ||||||
East 3 |
I would think that if I could have a "master" date slicer/filter that could look at both create and closed dates combined?
Is this possible?
EDIT: I forgot a very important field I need to include. I want to be able to split the count of tickets by priority level (Priority 1 - 5).
yes, you seem to be on the right track. With USERELATIONSHIP you can switch the join temporarily to the Close Date.
@jaduncan0515 So normally to do something like that you would multiple date dimension tables. You probably want something like this as well:
Take a look at these two Quick Measures as I think you want something like them.
https://community.powerbi.com/t5/Quick-Measures-Gallery/Open-Tickets/m-p/409364
https://community.powerbi.com/t5/Quick-Measures-Gallery/Periodic-Billing/m-p/409365
Thank you @Greg_Deckler . I'd seen your posts you referenced before actually, but forgot about them!
I've never used multiple DateDims before, but I did add a caveat to my post that I neglected - I need to split these ticket counts out by priority level using another field. There may also be other fields I want to be able to report on in the future as well. Is there a way to pull in that kind of meta data?
@jaduncan0515 I don't see that being a problem, it's just another filter.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
If you love stickers, then you will definitely want to check out our Community Sticker Challenge!
User | Count |
---|---|
142 | |
71 | |
64 | |
52 | |
50 |
User | Count |
---|---|
208 | |
91 | |
62 | |
59 | |
56 |