Is there a way to have a "master" date filter?

[jaduncan0515]

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.

• My fact table contains a create_date and a closed_date field.
• I have a date table with an active relationship to create_date and an inactive relationship to closed_date

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).

[lbendlin]

yes, you seem to be on the right track.  With USERELATIONSHIP you can switch the join temporarily to the Close Date.

[Greg_Deckler]

@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

[jaduncan0515]

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?

[Greg_Deckler]

@jaduncan0515 I don't see that being a problem, it's just another filter.