Custom Table: Continuous Record

[Pavlous]

Hi,

I have to made a new table, but I am really terrible at those custom tables. Lets say, that I want to track life cycle of projects. So I need to have listed all items with all months in which they live. But from the source I have only time stamps.

Project	Status	Date of Implementation
NRG-1	A	01/01/2018
NRG-1	B	01/03/2018
NRG-1	C	01/05/2018
NRG-2	A	01/02/2018
NRG-2	B	01/03/2018
NRG-2	C	25/03/2018
NRG-1	D	12/12/2018
NRG-1	E	01/01/2019
NRG-2	C	03/05/2018
NRG-2	D	20/12/2018
NRG-3	A	01/01/2018
NRG-3	B	20/01/2018
NRG-3	C	22/01/2018
NRG-3	D	05/02/2018
NRG-3	E	06/03/2018

And I need to return values with all the statuses until they changed

Project	Status	Month
NRG-1	A	Jan-18
NRG-3	A	Jan-18
NRG-3	B	Jan-18
NRG-3	C	Jan-18
NRG-2	A	Feb-18
NRG-3	D	Feb-18
NRG-1	A	Feb-18
NRG-1	B	Mar-18
NRG-2	B	Mar-18
NRG-3	E	Mar-18
NRG-2	C	Mar-18
NRG-1	B	Apr-18
NRG-2	C	Apr-18
NRG-3	E	Apr-18
...	...	...

Any help appreciated

[v-yuezhe-msft]

@Pavlous,

I am not very clear about the logic you use to create the second table based on the first table. Could you please explain more about it? For example, how do you get the following record in the second table? 

NRG-1

A

Feb-18

Regards,
Lydia

[Pavlous]

Hi @v-yuezhe-msft,

thank you for your reply.

Thats something I would like to do within the custom table. So it should be the result based on datas from the first data.

In other words, I need to "fill" all months between time stamps per project and with the status until its changed.

I am sure, its doable, as I have similiar function for other solution. Unfortunatelly I cant replicate it for this one, as I forgot the logic of it 😕

Result = 

VAR calendar_date =
    CALENDAR (
        CALCULATE ( FIRSTDATE (Archive[Received Date]), **filter** ),
        CALCULATE (  LASTDATE('CAS Tasks'[EndTime]), **filter** ))
		//^^ this part takes first and last relevant date 
    
VAR filted =
    FILTER ( ALL ( 'CAS Tasks' ), [Category] = **filter**)  
VAR filted2=
	FILTER(ALL('Archive'),[Categories] = **filter** )  
RETURN                   
    ADDCOLUMNS (
        calendar_date,
        "Total", (SUMX (
            FILTER (
                ADDCOLUMNS (
                    filted,
                    "Exist", CONTAINS (
                        SELECTCOLUMNS ( CALENDAR ( [Received Date], 'CAS Tasks'[EndTime] ), "CheckDate", [Date] ),
                        [CheckDate], [Date]
                    )
                ),
                [Exist] = TRUE ()
            ),
            'CAS Tasks'[To process corr.]  
    ))+
 (
    SUMX (
        FILTER (
            ADDCOLUMNS (
                filted2,
                "Exist2", CONTAINS (
                    SELECTCOLUMNS ( CALENDAR ( [Received Date], [End Time] ), "CheckDate2", [Date] ),
                    [CheckDate2], [Date]
                )
            ),
            [Exist2] = TRUE ()
        ),
        Archive[To process corr.]
    )
)
        )