Expand table rows by dates range from next row with group

[Ju743878]

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

[amitchandak]

@Ju743878 , 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-bd52912a5b...
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.

[Ju743878]

Hi, 

Thanks for your suggestion but unfortunatelly it's not the solution i aking for.

I want to expand the table row from date to next date row by ID, dynamicly...

BR