cancel
Showing results for
Did you mean:
Highlighted
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 Items Status Created Date Running Age Item 1 Open 04/02/2019 365 Item 2 Open 05/03/2019 336 Item 3 Open 06/04/2019 304 Item 4 Open 07/05/2019 273 Item 5 Open 08/06/2019 241 Item 6 Open 09/07/2019 210 Item 7 Open 10/08/2019 178 Item 8 Open 11/09/2019 146 Item 9 Open 12/10/2019 115 Item 10 Open 13/11/2019 83 Item 11 Open 14/12/2019 52 Item 12 Open 15/01/2020 20

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
 Date Open Items (total count) Open items older than 6 months % of items older than 6 months Nov 2019 10 4 40% Dec 2019 11 5 45% Jan 2020 12 6 50% Feb 2020 12 6 50%

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User IX

## 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

Proud to be a Super User!

4 REPLIES 4
Highlighted
Super User IX

## 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

Proud to be a Super User!

Highlighted
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 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

Highlighted
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

## Re: Age of tickets % - Month to Month Trend

thanks. that has worked a treat

Announcements

#### Power Platform Community Conference

Check out the on demand sessions that are available now!

#### Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

#### Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021