Calculate ticket counts based on date and categories

[Anonymous]

I have the following table structure:


Tickets

- TicketID

- StartDate

- EndDate


Calendar

- Date


TicketDetails

- TicketID

- IsMasterTicket

- ItilCategory

- Priority


I wanted to create a graph that shows for every day the currently amount of open tickets based on itil category and priority.


I tried to do this with a calculated column in the calendar:

CALCULATE(  COUNT(Tickets[TicketID]),
            FILTER(
                Tickets, Tickets[StartDate] < 'Calendar'[Date]
                && (Tickets[EndDate] > 'Calendar'[Date] )
                )
           )

But this show me only the total amount.

Now I dont know how to go forward because:

- With this method I need to create a new column for every type of ITIL / Priority, but this is very static, I wanted to have a more dynamically way.

- I want to give the possibility to select different customers in a slicer, with my normal charts the slicer works just fine but in this calculation no matter what I select the calculation is always in total.


What would be the best practise to do this kind of calculation?


I use Import Mode and MS SQL Server as source.


Thanks in advance.

[v-lili6-msft]

hi, @Anonymous

There is a similar case for you to refer to. For a dynamically way, you may try to create a measure instead of a column.

Reference:https://community.powerbi.com/t5/Quick-Measures-Gallery/Periodic-Billing/m-p/409365

Best Regards,

Lin