Reform table

Question

I have a data that records the member upgrade assessment of our member as below:&nbsp;customer&nbsp; &nbsp;date&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; prev_member_level&nbsp; &nbsp; current_member_levelJohn&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1Jan2017&nbsp; &nbsp; &nbsp; &nbsp;Classic&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;GoldJohn&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 4Apr2017&nbsp; &nbsp; &nbsp; Gold&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Gold&nbsp;John&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;15 Jun2017&nbsp; &nbsp; Gold&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; PlatinumJohn&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1Dec2017&nbsp; &nbsp; &nbsp; Platinum&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; GoldMary&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;2Feb2017&nbsp; &nbsp; &nbsp; Silver&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Gold&nbsp;How can i reform the information above to be like table below?&nbsp;customer&nbsp; &nbsp;start_date&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; end_date&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;member_levelJohn&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1Jan2016&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 31Dec2016&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ClassicJohn&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1Jan2017&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 14Jun2017&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;GoldJohn&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;15Jun2017&nbsp; &nbsp; &nbsp; &nbsp; Today()&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; PlatinumMary&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1Jan2016&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1Feb2017&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;SilverMary&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 2Feb2017&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 6Apr2018&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Gold

phil_seamark · Accepted Answer

HI Anonymous&nbsp;This calculated table is getting close.&nbsp; There is a bit going on, but I think it addresses most of the quirks.&nbsp;I have attached a PBIX file as well&nbsp;New Table = 
VAR RetVal = 
        ADDCOLUMNS(
            'Table',
            "Next Date",
            MINX(
                FILTER(
                    'Table',
                    'Table'[Customer] = EARLIER('Table'[Customer]) &amp;&amp; 
                    'Table'[Date] &gt; EARLIER('Table'[Date]) &amp;&amp; 
                    [pre_member_level]&lt;&gt; [current_member_level]
                    ),
                    [Date]-1)
                    )
VAR cleanTable = FILTER(RetVal,[current_member_level]&lt;&gt;[pre_member_level])

VAR Step2 =  
    SELECTCOLUMNS(
        cleanTable ,
        "Customer" , [Customer] ,
        "Start_date" , [Date] ,
        "end_date" , if( 
                            ISBLANK([Next Date]),
                            TODAY(),
                            [Next Date]) ,
        "member_level" , [current_member_level]

)

VAR FirstLines =
    SELECTCOLUMNS( 
        GENERATE(SELECTCOLUMNS(SUMMARIZECOLUMNS('Table'[Customer],"end_date" , MIN('Table'[Date])),"C",[Customer],"D",[end_date]),filter('Table','Table'[Customer]=[C] &amp;&amp; 'Table'[Date]=[D])),
        "Customer",[Customer] ,
        "Start_date",DATE(2016,1,1) ,
        "End_date",[Date]-1,
        "member_level",[pre_member_level]
    )
        
RETURN    UNION( FirstLines,Step2)
    &nbsp;

Forum Discussion

Reform table

2 Replies

Related content

Reformating a table by removing duplicate rows

Matrix table

Lakehouse Table Generate Create Table

Tables combine

Table Transformation