Issue with Counting Tickets and Incidents Using Calendar Table

Hi bayremsyoud 

Welcome to the Microsoft Fabric Forum,
 

Regarding the Issue with Counting Tickets and Incidents Using Calendar Table

While I may not have full visibility into the specific structure of your dataset, I have created a sample .pbix file to demonstrate one possible approach to implementing the desired logic.

 I’ve included relevant screenshot and attached the .pbix file for your reference. Please take a moment to review them and see if this solution aligns with your requirements.

If this doesn’t fully meet your needs, could you kindly share a sample of your data and more detailed context? That would help us provide a more accurate solution.

If this response resolves your query, kindly mark it as Accepted Solution to help other community members. A Kudos is also appreciated if you found the response helpful.

Thank You!

Hello bayremsyoud 

I have tried the following steps to address the issue:

1. Create a calendar table generated from the MIN and MAX of all dates:

         Calendar Table:

	Calendar = CALENDAR(
	    MINX(UNION(SELECTCOLUMNS(Ticket, "Date", Ticket[Created Date]), SELECTCOLUMNS(Incident, "Date", Incident[Start Date])), [Date]),
	    MAXX(UNION(SELECTCOLUMNS(Ticket, "Date", Ticket[Closed Date]), SELECTCOLUMNS(Incident, "Date", Incident[End Date])), [Date])
	)

     2. Create measures for Ticket Created, Ticket Closed, Incident Start Date, and Incident End Date:

       Tickets Created and Tickets Clsoed:
      

Tickets Created
	
Tickets Created = 
	CALCULATE(
	    COUNT(Ticket[TicketID])  
	)
	
Tickets Closed
	
Tickets Closed = CALCULATE(
	    COUNTROWS(Ticket),
	    USERELATIONSHIP(Calendar[Date], Ticket[Closed Date])
	)

     Incidents Started and Incidents Ended:

    

Incident Started	

Incidents Started = COUNTROWS(Incident)
	

Incident Ended	
	
Incidents Ended = CALCULATE(
	    COUNTROWS(Incident),
	    USERELATIONSHIP(Calendar[Date], Incident[End Date])
)

Please use the above measures in the table visual.

Additionally, it might be helpful to install the latest version of Power BI Desktop to ensure compatibility and access to the latest features. You can download the latest version from the official Power BI website.

Thank You!