Hi,
I have a table containing many years of transaction history with 100's of serial numbers. The serial numbers have been in several different statuses.
I want to count days per period (per week, month, year) where:
1. The specific serial numbers have been in Redress Status, based by dates between: Startdate: Status "Redress In" to End Date: Status "Redress Out".
2. The specific serial numbers are in Redress Status now ("Redress In" Date to today), based on where the last status on serial number is "Redress In"
I already have the "Count days" result based on my own columns/measures, but my problem is that I can't figure out how to count these days per e.g Month. If you look at row 2 in my example, I need the result to display 17 days in Sept and 4 days in Aug. As it is now, I will get 21 days in September, which is not correct when I want to calculate avg days pr month- or other periods.
Please note that there can be several transactions in one day, so the dates need to be based on index.
Serial No Status Index DT Days on Redress
| 311381 | Redress In | 8535450 | 27.08.2020 00:00 | |
| 311381 | Redress Out | 8644891 | 17.09.2020 00:00 | 21 |
| 296855 | Redress In | 9045163 | 30.11.2020 00:00 | |
| 296855 | Redress Out | 9106069 | 10.12.2020 00:00 | 10 |
| 296855 | Redress In | 9366707 | 29.01.2021 00:00 | 34 |
| 311381 | Redress In | 9431940 | 09.02.2021 00:00 | 23 |
