cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper IV
Helper IV

Age of tickets % - Month to Month Trend

Hi

appreciate any help and hopefully i can explain this well enough

 

looking to provide a month to month % trend graph of tickets open longer than 6 months (or 183 days) against the total count of all tickets
seems simple enough and ive been using a custom column to produce a running age of the item. this works fine when i want to categorise the age of items in open in day ranges or simply show how long something has been open for each day based on todays date

the below is a sample of how my data is broken up
"Running Age" is simply a column Datediff on Todays date minus the created date to get days open for

 

Work ItemsStatusCreated DateRunning Age
Item 1Open04/02/2019365
Item 2Open05/03/2019336
Item 3Open06/04/2019304
Item 4Open07/05/2019273
Item 5Open08/06/2019241
Item 6Open09/07/2019210
Item 7Open10/08/2019178
Item 8Open11/09/2019146
Item 9Open12/10/2019115
Item 10Open13/11/201983
Item 11Open14/12/201952
Item 12Open15/01/202020


my formula for calculating the count of items older than 6 months is

Running Age (Open > 6 months) =
CALCULATE(
COUNTROWS('Work Items'), FILTER('Work Items','Work Items'[Running Age] > 183))
 
then use that to divide against the total in this measure
Running Age (% Total - > 6 months) =
var total = Calculate(COUNT('Work Items'[Work Items]))
Return
Divide([Running Age (Open > 6 months)], total, blank())

when i want to determine the % trend for previous months i get stuck. Currently when the report updates it factors in what todays date is, rather than that month in time i.e. in Nov 2019

 

trying to work out how i can show that snapshot in time of what was older than 6 months? i am assuming i have been using the running age incorrectly

 
Expected output would be somethign similar to below but just use the date & last column in a area chart
DateOpen Items (total count)Open items older than 6 months% of items older than 6 months
Nov 201910440%
Dec 201911545%
Jan 202012650%
Feb 202012650%
    
 
1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User IV
Super User IV

Re: Age of tickets % - Month to Month Trend

Use date calendar and formula like this

 

Running Age (Open > 6 months) =
CALCULATE(
COUNTROWS('Work Items'), FILTER('Work Items', datediff(	Created Date,max(date[date]),Month)> 6))

 

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners , HR-Analytics-Active-Employee-Hire-and-Termination-trend
Power-BI-Working-with-Non-Standard-Time-Periods And Comparing-Data-Across-Date-Ranges

Connect on Linkedin



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

View solution in original post

4 REPLIES 4
Highlighted
Super User IV
Super User IV

Re: Age of tickets % - Month to Month Trend

Use date calendar and formula like this

 

Running Age (Open > 6 months) =
CALCULATE(
COUNTROWS('Work Items'), FILTER('Work Items', datediff(	Created Date,max(date[date]),Month)> 6))

 

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners , HR-Analytics-Active-Employee-Hire-and-Termination-trend
Power-BI-Working-with-Non-Standard-Time-Periods And Comparing-Data-Across-Date-Ranges

Connect on Linkedin



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

View solution in original post

Highlighted
Helper IV
Helper IV

Re: Age of tickets % - Month to Month Trend

@amitchandak 

thanks for the help. i might be missing something. i did a manual count of the current data and the end calculation using your formula isnt the same
in comparison, the data driven by your formula only goes back to Oct/Nov 
as per below graphs,
left one shows my the total of all issues against the ones older than 6 months. but incorrectly factors in todays date minus 6 months
the right is showing the output of your formula. not sure why its only going back to Oct/Nov period??


My formulaMy formulaNew formulaNew formula

 

essentially the graph should look something like the below with the blue line showing the count of items per month older than 6 months compared against the total of all issues
Issue 3.PNG

 

 

Highlighted
Helper IV
Helper IV

Re: Age of tickets % - Month to Month Trend

SCRAP ABOVE!!!

 

realised i was using a wrong date field

ill run the report and compare against manual data and report back!

Highlighted
Helper IV
Helper IV

Re: Age of tickets % - Month to Month Trend

thanks. that has worked a treat

 

@amitchandak  thanks for your help

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Tech Marathon

Maratón de Soluciones de Negocio Microsoft

Una semana de contenido con +100 sesiones educativas, consultorios, +10 workshops Premium, Hackaton, EXPO, Networking Hall y mucho más!

Top Solution Authors
Top Kudoed Authors