Distinct calculation of dates within a range

[andi2333]

Hello, I require support on the following issue (quite new to PBI, hence not entirely sure if there is a simple solution to this).

I have a table which essentially carries ticket IDs, some ticket metadata (e.g. severity, accountable team), and two date fields: (ticket_opened) and (ticket_closed). The latter one could of course be empty in case the ticket has not been solved yet.

I know want to create a line chart showing the active tickets per calendar week. My problem is two-fold:

1. Date Hierarchy in PowerBi does not seem to include calendar weeks? - is there a trick to get this view?

2. I dont know how to distincly calculate the respective rows. 

Example: my table has the following entries:

                  opened              closed

INC0001    01.01.2023         05.01.2023

INC0002   01.01.2023          03.01.2023

INC0003    02.01.2023          04.01.2023

This means I had three open tickets in calendar week 1. However, if I do a sum of countrows, I get a much higer count as it counts each day and then sums up the value for the week.

Any support is much appreciated. Thank you.

[BA_Pete · Accepted solution]

Hi @andi2333,

To point 1: You'll need to set up a proper calendar table (I'd recommend in Power Query) and set it up with whichever date dimension you want to use. There's loads of tutorials online how to do this, so I'll no go into any further depth here.

Point 2: Once you've got your calendar table, you can create a measure like this to get open tickets at the end of any given date dimension period:

_openTicketsOverTime =
VAR __cDate = MAX(calendar[date])
RETURN
CALCULATE(
    DISTINCTCOUNT(yourTable[TicketID]),
    FILTER(
        yourTable,
        yourTable[opened] <= __cDate
        && ( yourTable[closed] > __cDate || ISBLANK(yourTable[closed]) )
    )
)

Just make sure to use the period dimension from your calendar table on the chart axis.

Pete