Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
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
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% |
Solved! Go to Solution.
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
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
@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??
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
SCRAP ABOVE!!!
realised i was using a wrong date field
ill run the report and compare against manual data and report back!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
97 | |
85 | |
67 | |
59 |
User | Count |
---|---|
150 | |
120 | |
100 | |
87 | |
68 |