cancel
Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Reply
nti-cristiano
Frequent Visitor

Filter the tickets which have the same opened/closed date

Hi guys!!

 

Today I can filter (closed tickets or opened tickets) in my tickets system by the date using two separated measures. I'm not able to get tickets which have the same date/period of "Opened_Date" and "Closed_Date" according the dCalendar table

 

DETAILS of my filters and tables that I use today:

 

dCalendar = table used to filter by date, month, year etc.

 

Tickets Opened = COUNTROWS(dTicket)

 

Tickets Closed = CALCULATE(COUNTROWS(dTicket);USERELATIONSHIP(dTicket[Closed_Date];dCalendar[Date]))

 

PS.: I use USERELATIONSHIP because the table "dTicket" have the two columns ("Opened_Date" and "Closed_Date") connected to dCalendar[Date] so I have active/inactive relationship.

 

How can I get/filter the tickets which have the same date in the "opened_date" and "closed_date" simultaneously as I change the date in the visual?

1 ACCEPTED SOLUTION
nti-cristiano
Frequent Visitor

Hi everyone!!

 

Finnaly, I got this problem resolved on my own!! If someone is facing this problem, I share the solution that I figured out:

 

Total Closed on Same Date =
CALCULATE(COUNTROWS(dTicket); FILTER(dTicket; AND(dTicket[Created_Date]<=MAX(dCalendar[Date]);dTicket[Created_Date]>=MIN(dCalendar[Date])));FILTER(dTicket; AND(dTicket[Closed_Date]<=MAX(dTicket[Created_Date]);dTicket[Closed_Date]>=MIN(dTicket[Created_Date]))))

 

 

View solution in original post

5 REPLIES 5
nti-cristiano
Frequent Visitor

Hi everyone!!

 

Finnaly, I got this problem resolved on my own!! If someone is facing this problem, I share the solution that I figured out:

 

Total Closed on Same Date =
CALCULATE(COUNTROWS(dTicket); FILTER(dTicket; AND(dTicket[Created_Date]<=MAX(dCalendar[Date]);dTicket[Created_Date]>=MIN(dCalendar[Date])));FILTER(dTicket; AND(dTicket[Closed_Date]<=MAX(dTicket[Created_Date]);dTicket[Closed_Date]>=MIN(dTicket[Created_Date]))))

 

 

Hi Cristanio, I'm facing the same problem as you have encountered. But I have applied the solution which you have given but data is not matching. If possible can you please share PBIX file or you can explain in detail.

Thank you.

SRRY.


@nti-cristiano wrote:

Hi everyone!!

 

Finnaly, I got this problem resolved on my own!! If someone is facing this problem, I share the solution that I figured out:

 

Total Closed on Same Date =
CALCULATE(COUNTROWS(dTicket); FILTER(dTicket; AND(dTicket[Created_Date]<=MAX(dCalendar[Date]);dTicket[Created_Date]>=MIN(dCalendar[Date])));FILTER(dTicket; AND(dTicket[Closed_Date]<=MAX(dTicket[Created_Date]);dTicket[Closed_Date]>=MIN(dTicket[Created_Date]))))

 

 


 

nti-cristiano
Frequent Visitor

I think this problem there is no solution I tried everything if someone can help me I'll apreciate! 

parry2k
Super User
Super User

@nti-cristiano if I understood it correctly you can do something like this

 

Add following column

Same Day Close Flag = IF( Table[OpenDate] = Table[CloseDate], 1, 0 )

Add measure to get count of same day close

 

Total Closed on Same Day = SUM( Table[Same Day Close Flag] )


Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Hi @parry2k ,

 

Thanks for the help! I just tested creating the column and the measure as you said but didn't work. I realized why, actually it just returns TRUE if the the dates (opened and closed) are exacly the same, like 07/02/2019 = 07/02/2019 equal TRUE,  but what I need is to compare the dates using the table dCalendar clicking on the dates in the visual, the dCalendar can be used by month, day, year etc to filter the table dTicket. Example using dCalendar by month I need to know how many tickets were opened in the month and how many were closed in this same month.

Helpful resources

Announcements
PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors