Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
jaduncan0515
Frequent Visitor

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

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      
idparent_idcreate_dateclosed_datecurrent_statusmarketOpen/Closed (Calc Column)
880664 1/1/20232/1/2023ClosedWest 1Closed
8806658806641/1/20232/1/2023ClosedWest 1Closed
8806668806642/2/20233/22/2024ClosedWest 2Closed
8806778806643/20/20243/22/2024ClosedWest 2Closed
8806788806643/21/20243/27/2024ClosedWest 3Closed
8806798806643/5/20243/27/2024ClosedWest 3Closed
880660 1/1/2023 Alarm Clear ReceivedEast 1Open
8806618806605/1/2023 InvestigatingEast 1Open
8806628806603/22/2024 Alarm Clear ReceivedEast 2Open
8806688806603/24/2024 InvestigatingEast 2Open
8806698806603/5/2024 Alarm Clear ReceivedEast 3Open
8806708806603/27/2024 Alarm Clear ReceivedEast 3Open

 

Date Table     
DateYearQuarterMonthWeekWeekday
1/1/20242024111Monday

 

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 WeekCreated Last WeekClosed Last WeekClosed Last WeekOpen Tickets Older than Week 12Open Tickets Older than Week 12
MarketDistinct Count of parent_idDistinct Count of idDistinct Count of parent_idDistinct Count of idDistinct Count of parent_idDistinct 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).

 

 

4 REPLIES 4
lbendlin
Super User
Super User

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

Greg_Deckler
Super User
Super User

@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



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

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.



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.