Forum Discussion
Search multiple date fields
Hello,
I have a table Tickets, that has ID, owner, date created, date closed, date escalated, etc. I want the user to be able to pick a date range from a slicer and have multiple cards that show how many tickets created, closed, escalated, etc within that time frame. I'm confused as to how to set that up? I presently have a Tickets table and a Date table, but only Date Created is linked to the Date table.
Thanks.
Hi aashton
You could only create one active relationship between two tables( only Date Created is linked to the Date table).
Here I suggest you calcualte the count of Tickets which are in date range by measure.
My Sample:
Relationship is the same like yours.
Meausres:
Count of date created = COUNT('Sample'[date created])+0Count of date closed = VAR _DateRange = VALUES('Date'[Date]) RETURN CALCULATE(COUNT('Sample'[ID]),FILTER(ALL('Sample'),'Sample'[date closed] IN _DateRange))+0Count of date escalated = VAR _DateRange = VALUES('Date'[Date]) RETURN CALCULATE(COUNT('Sample'[ID]),FILTER(ALL('Sample'),'Sample'[date escalated] IN _DateRange))+0Result is as below.
Date Range 9/28/2021 to 10/8/2021:
Date = 10/8/2021
Best Regards,
Rico ZhouIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 Replies
aashton
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/409365Hi aashton
You could only create one active relationship between two tables( only Date Created is linked to the Date table).
Here I suggest you calcualte the count of Tickets which are in date range by measure.
My Sample:
Relationship is the same like yours.
Meausres:
Count of date created = COUNT('Sample'[date created])+0Count of date closed = VAR _DateRange = VALUES('Date'[Date]) RETURN CALCULATE(COUNT('Sample'[ID]),FILTER(ALL('Sample'),'Sample'[date closed] IN _DateRange))+0Count of date escalated = VAR _DateRange = VALUES('Date'[Date]) RETURN CALCULATE(COUNT('Sample'[ID]),FILTER(ALL('Sample'),'Sample'[date escalated] IN _DateRange))+0Result is as below.
Date Range 9/28/2021 to 10/8/2021:
Date = 10/8/2021
Best Regards,
Rico ZhouIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
