Forum Discussion
Search a specific date range without using date column as a filter
That kept me entertained for an hour or so :-)
https://www.dropbox.com/s/b1beaapdjtyp3ay/split%20days%20per%20month.pbix?dl=0
I am not saying this is the best way to do it, but it seems to work. I created a pattern of 4 steps to extract the days for the current month and find any remainder. I then copied these 4 steps multiple times (manually) to iterate, creating a new set of columns each time. I created 1 extra set than needed allowing for growth. The problem is that you will need to extend it further if a ticket is open for longer than 6 months.
Anyway, see what you think.
You should look at using a calendar table instead of converting your dates to months. http://exceleratorbi.com.au/power-pivot-calendar-tables/
Now you say that your date data only captures when there is an edit. So do you have any date data other than this? What is the logic that exisits in your data that can determine what to show for the last 3 months if it is not the Edit date column?
You can only display based on the data you have. So how do you do it in Excel?
That is what I thought. In excel we export all the history data and perform the calculation in excel to show duration than go through the list and find where a status carried over an entire month an update that for the month. Such as serial number 1 was in same status for 40 days. For the month we report on we state it was in that status for 30 days. Quite a manual process so was hoping BI had a solution.
Yes, BI has a solution. You can create a new column in Power Query (get data). You will need to rebuild the logic from Excel as an "Add Column" in Power Query. If you don't know how to do that, you could post some sample data in an Excel workbook with the correct logic you are using for someone to take a look