Hi everyone,
I need your help, I am trying to count up to a date the number of active days of IDs, for example:
ID | CONTRACT | START | END |
1 | 1 | 01-01-2016 | 01-06-2016 |
2 | 2 | 01-01-2016 | 31-12-2016 |
1 | 3 | 01-01-2019 | 01-01-2020 |
3 | 4 | 01-01-2017 | 01-01-2018 |
1 | 5 | 01-08-2016 | 01-08-2017 |
4 | 6 | 01-05-2017 | 01-05-2018 |
3 | 7 | 01-03-2017 | 01-03-2018 |
If we consider the date "01-01-2018", the number of days accumulated by ID is:
* ID = 1: As of 01-01-2018 ID "1" has 517 accumulated days, contract 1 provides 152 days and contract 5 with 365 days, contract 3 starts after the cut-off date, so it is not considered.
ID | CONTRACT | START | END |
1 | 1 | 01-01-2016 | 01-06-2016 |
1 | 3 | 01-01-2019 | 01-01-2020 |
1 | 5 | 01-08-2016 | 01-08-2017 |
* ID = 3: As of 01-01-2018 ID "3" has 365 days accumulated, noting that several days are active contract 4 and 7, however, counts as only 1.
ID | CONTRACT | START | END |
3 | 4 | 01-02-2017 | 01-01-2018 |
3 | 7 | 01-03-2017 | 01-03-2018 |
Finally, the result is:
ID | CONTRACT | START | END | COUNT_DAY |
1 | 1 | 01-01-2016 | 01-06-2016 | 517 |
2 | 2 | 01-01-2016 | 31-12-2016 | 365 |
1 | 3 | 01-01-2019 | 01-01-2020 | 334 |
3 | 4 | 01-01-2017 | 01-01-2018 | 334 |
1 | 5 | 01-08-2016 | 01-08-2017 | 517 |
4 | 6 | 01-05-2017 | 01-05-2018 | 120 |
3 | 7 | 01-03-2017 | 01-03-2016 | 334 |
I would appreciate your suggestions for calculating in DAX.
Thank you
Karlo
