Measure to work out count of tickets open at end of month

Anything related to PowerPivot and DAX Formuale
macca333
Posts: 1
Joined: Thu May 09, 2019 6:23 pm

Measure to work out count of tickets open at end of month

Postby macca333 » Thu May 09, 2019 7:10 pm

I have an report set up to analyse the tickets we have opened and closed in a set time period, for the source data each row will will have a date and time the ticket was open and resolved.

What i want to do is create a measure that will give me a count of how many tickets were open at the end of each month, IE:

target.PNG
target.PNG (9.62 KiB) Viewed 287 times


this is done manually by just subtracting the resolved count from the open count (for first month) and adding the open count from the previous month. This is semi accurate but doesn't take into account tickets that were opened prior to the period data is extracted for(data extracts based on resolved date) i only want to show the open counts for my desired period. I also don't want to have to extract all the data each time i run the report as this goes back quite a while, i just want to extract ticket that were either opened or resolved during my desired period.

What i want the measure to do is check the resolved date against the pivot tables current month and count all the incidents that were opened that month or before and were resolved AFTER that month), I've tried a few different ways by using Calculate and Filter() but it always wants to give me a count of incidents resolved that month. I cant figure out how to say 'compare this row in data table date against the pivot table year and month'.

The dates in pivot table are provided by a calendar table linked to open date on the main data table(can only link to either open or resolved date so i created another table with just the resolved dates and linked this to the calendar table as well so get accurate resolved counts)

I've attached some sanitised sample data to give idea of how the data is extracted.
Attachments
sample file.xlsx
(9.28 KiB) Downloaded 13 times

MattAllington
Posts: 1053
Joined: Sun May 04, 2014 4:01 pm
Location: Sydney, Australia

Re: Measure to work out count of tickets open at end of month

Postby MattAllington » Sun May 12, 2019 8:07 am

This is genetically called “events in progress”. Read here. https://www.sqlbi.com/articles/analyzin ... on-in-dax/
Matt Allington is Self Service BI Consultant, Trainer and Author of the Book "Supercharge Power BI".
Power BI Training


Return to “PowerPivot/DAX”

Who is online

Users browsing this forum: No registered users and 0 guests