topic open tickets on weekly basis in DAX Commands and Tips

open tickets on weekly basis

JC2022 — Thu, 27 Feb 2025 15:10:54 GMT

Hi,
I have a table in Power BI with the structure like the table below. Now I would like to see in a visual the amount of open cases per week number. So week numbers on the x-axis. The amount of open cases should be based on the Creation_Date (opened) and the Finish_Date (closed).

Case	Description	Category	Status	Priority	Creation_Date	Resolved_Date	Finish_Date
CS55559	Switches	LAN	Closed	2	19-12-2024	19-12-2024	19-12-2024
CS77778	WLAN02	Networking	Closed	3	13-12-2024	20-1-2025	3-2-2025
CS00015	Encryption	Wireless	Closed	3	14-11-2024	19-11-2024	19-11-2024
CS99995	Update	WLAN	Closed	1	7-11-2024	7-11-2024	21-11-2024

Re: open tickets on weekly basis

mark_endicott — Thu, 27 Feb 2025 15:44:21 GMT

JC2022 - You'll need to add a date table with week numbers included, this is great article for that: https://forum.enterprisedna.co/t/extended-date-table-power-query-m-function/6390

And then this article will help you with defining when something was open: https://www.daxpatterns.com/events-in-progress/

If I answered your question please mark my post as the solution, it helps others with the same challenge find the answer!

Re: open tickets on weekly basis

JC2022 — Thu, 27 Feb 2025 17:01:37 GMT

mark_endicott this works except from the fact that my dataset is too big?
Is there anything I can do about it?

Re: open tickets on weekly basis

mark_endicott — Thu, 27 Feb 2025 17:15:10 GMT

JC2022 - Filter the data down to that which matters. What you are trying to do is incredibly resource hungry.

The DAX patterns article has a snapshot example too. If filtering does not resolve your challenge, you should consider this as an option.

If I answered your question please mark my post as the solution, it helps others with the same challenge find the answer!