Hi @luzrueda,
Thanks for reaching out to the Microsoft fabric community forum.
To achieve the weekly count of open tickets, you’ll need to generate a row for each week a ticket is open, and then count how many tickets are open per week.
Here’s how you can approach this:
* Create a separate table with all Mondays (or any consistent weekday) from the earliest start date (e.g., 01.01.2025) to today. Each row represents the start of a week.
* Normalize your ticket data, for tickets with no End Date, replace it with TODAY() or the last date in your calendar table. Make sure both Start and End Dates are in Date format.
* Create a Mapping Table (Ticket-Week Matrix), for each ticket, generate all weeks between Start and End Date. In Excel/Power Query, this can be done with a custom function or using Power BI with a GENERATE + CALENDAR or ADDCOLUMNS.
DAX:
TicketWeeks =
GENERATE(
Tickets,
ADDCOLUMNS(
CALENDAR(
STARTOFWEEK(Tickets[Start Date], 2),
IF(
ISBLANK(Tickets[End Date]),
TODAY(),
STARTOFWEEK(Tickets[End Date], 2)
)
),
"WeekStart", [Date]
)
)
This will give you one row per week that each ticket is active.
* Now you can create a measure or simple count grouped by WeekStart to see how many tickets are open each week. And for visualization part use the WeekStart as the X-axis and the count of tickets as the Y-axis in a Bar Chart.
If I misunderstand your needs or you still have problems on it, please feel free to let us know.
Best Regards,
Hammad.
