Forum Discussion

Pavlous's avatar
Pavlous
Advocate II
8 years ago

Custom Table: Continuous Record

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 th...
Need Help
Anonymous's avatar
Anonymous
Not applicable
8 years ago

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's avatar
Pavlous
Advocate II
8 years ago

Hi Anonymous,

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.]
    )
)
        )