Forum Discussion
Expand table rows by dates range from next row with group
Hi everyone, hope your are good !
I struggle with some manipulation :
In this i.e i want to transform dynamically this table :
| ID | DT_DATE | TP_EVENT |
| 1 | 01/01/2023 | OPEN |
| 1 | 14/01/2023 | CLOSE |
| 1 | 15/01/2023 | RE-OPEN |
| 1 | 15/01/2023 | EXCEPTION |
| 1 | 18/01/2023 | CLOSE |
| 1 | 20/01/2023 | RE-OPEN |
| 2 | 05/01/2023 | OPEN |
| 2 | 10/01/2023 | EXCEPTION |
| 2 | 17/01/2023 | CLOSE |
| 3 | 17/01/2023 | OPEN |
| 3 | 25/03/2023 | CLOSE |
To this :
| ID | DT_DATE | TP_EVENT |
| 1 | 01/01/2023 | OPEN |
| 1 | 02/01/2023 | OPEN |
| 1 | 03/01/2023 | OPEN |
| 1 | 04/01/2023 | OPEN |
| 1 | 05/01/2023 | OPEN |
| 1 | 06/01/2023 | OPEN |
| 1 | 07/01/2023 | OPEN |
| 1 | 08/01/2023 | OPEN |
| 1 | 09/01/2023 | OPEN |
| 1 | 10/01/2023 | OPEN |
| 1 | 11/01/2023 | OPEN |
| 1 | 12/01/2023 | OPEN |
| 1 | 13/01/2023 | OPEN |
| 1 | 14/01/2023 | CLOSE |
| 1 | 15/01/2023 | RE-OPEN |
| 1 | 15/01/2023 | EXCEPTION |
| 1 | 16/01/2023 | EXCEPTION |
| 1 | 17/01/2023 | EXCEPTION |
| 1 | 18/01/2023 | CLOSE |
| 1 | 19/01/2023 | CLOSE |
| 1 | 20/01/2023 | RE-OPEN |
| 2 | 05/01/2023 | OPEN |
| 2 | 06/01/2023 | OPEN |
| 2 | 07/01/2023 | OPEN |
| 2 | 08/01/2023 | OPEN |
| 2 | 09/01/2023 | OPEN |
| 2 | 10/01/2023 | EXCEPTION |
| 2 | 11/01/2023 | EXCEPTION |
| 2 | 12/01/2023 | EXCEPTION |
| 2 | 13/01/2023 | EXCEPTION |
| 2 | 14/01/2023 | EXCEPTION |
| 2 | 15/01/2023 | EXCEPTION |
| 2 | 16/01/2023 | EXCEPTION |
| 2 | 17/01/2023 | CLOSE |
| 3 | 17/01/2023 | OPEN |
| 3 | 18/01/2023 | OPEN |
| 3 | 19/01/2023 | OPEN |
| 3 | 20/01/2023 | OPEN |
| 3 | 21/01/2023 | OPEN |
| 3 | 22/01/2023 | OPEN |
| 3 | 23/01/2023 | OPEN |
| 3 | 24/01/2023 | OPEN |
| 3 | 25/03/2023 | CLOSE |
In DAX; power query or even SQL as you prefer, someone can help me ?
Thanks in advance !
BR
Julien
2 Replies
- amitchandak
Super User
Anonymous , Create a date table and join date of date with Dt_date
new table
Date = calendarauto()
then have measures like
calculate(lastnonblankvalue(Table[DT_DATE], max(tbale[TP_EVENT])), filter(all('Date'), 'Date'[Date]<= max('date'[Date])))
Why Time Intelligence Fails - Powerbi 5 Savior Steps for TI :https://youtu.be/OBf0rjpp5Hw
https://amitchandak.medium.com/power-bi-5-key-points-to-make-time-intelligence-successful-bd52912a5bd4
To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.
