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.
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
Thanks, not sure how the logic would work. In excel it was completely manual. If this serial number was in a status for 40 days than for that one we new it was 100% and would carry the remainder to the next month. Here is teh table I am working with, datediff is giving me the duration. But for duration over one month in time or duration that carry from one month to the next, I am trying to figure out how to show that duration was associated with individual month. The 122 date duration in my current report shows up to 122 days in April, when I would like it to show 122 days from April 19th till today. So approximately 10 days in april, 30 days in may, 30 days in june and 30 days in july. Roughly, have not refreshed report lately.
| Serial Number | Edit Date | Old Value | New Value | Index | Duration |
| 1 | 3/17/2016 8:22 | Non-Operational | Fully Operational | 27 | 11 |
| 1 | 3/28/2016 8:05 | Fully Operational | Reduced Throughput | 28 | 2 |
| 1 | 3/30/2016 7:11 | Reduced Throughput | Fully Operational | 29 | 9 |
| 1 | 4/8/2016 12:21 | Fully Operational | Reduced Throughput | 30 | 4 |
| 1 | 4/12/2016 7:55 | Reduced Throughput | Non-Operational | 31 | 2 |
| 1 | 4/14/2016 11:13 | Non-Operational | Fully Operational | 32 | 0 |
| 1 | 4/14/2016 11:54 | Fully Operational | Non-Operational | 33 | 5 |
| 1 | 4/19/2016 8:13 | Non-Operational | Fully Operational | 34 | 122 |
| 2 | 2/19/2016 16:40 | - | Down for Maintenance | 35 | 21 |
| 2 | 3/11/2016 13:53 | Down for Maintenance | Fully Operational | 36 | 21 |
| 2 | 4/1/2016 8:22 | Fully Operational | Reduced Throughput | 37 | 0 |
| 2 | 4/1/2016 8:59 | Reduced Throughput | Non-Operational | 38 | 6 |
| 2 | 4/7/2016 7:53 | Non-Operational | Fully Operational | 39 | 134 |
Current chart in report
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.