How to copy latest row and calculate cumulative total

[wuxxx711]

Hello,

I have a table as below, This table only updated if something changed (such as total or status). 

Item#	Status	Open date	Closed date	Updated date	Total
A123	O	12/1/2020		12/1/2020	$100
A123	C	12/1/2020	2/1/2021	2/1/2021	$50
A123	RO	12/1/2020		5/1/2021	$70
B456	O	2/1/2021		2/1/2021	$60
B456	C	2/1/2021	4/1/2021	4/1/2021	$40

What we need it to be is like below. To fill out the gap between updated date and calculate the cumulative table.

Item#	Status	Open date	Closed date	Updated date	Total
A123	O	12/1/2020		12/1/2020	$100
A123	O	12/1/2020		1/1/2021	$100	new
A123	C	12/1/2020	2/1/2021	2/1/2021	$150
A123	C	12/1/2020	2/1/2021	3/1/2021	$150	new
A123	C	12/1/2020	2/1/2021	4/1/2021	$150	new
A123	RO	12/1/2020		5/1/2021	$220
B456	O	2/1/2021		2/1/2021	$60
B456	O	2/1/2021		3/1/2021	$60	new
B456	C	2/1/2021	4/1/2021	4/1/2021	$100

The issue is there are more than 80000 item#, and some new item# be added every month.  I am new with Power BI, please help. Thanks in advance. @amitchandak 

[v-luwang-msft]

Hi @wuxxx711 ，

Has your problem been solved, if so, please consider Accept a correct reply as the solution to help others find it. 

Best Regards,

Lucien

[wuxxx711]

unfortunately, I have not solved this problem.😂

[v-luwang-msft]

Hi @wuxxx711 ，

In my opinion,What can be done at the moment is to split the calculation first and then merge the data at the end.

At first I created a separate date table to implement the row data addition, but when
When update_date=2/1/2020, Item# has A123 and B456, and the new date table update_date=2/1/2020 returns one of them. So I think the best way to do this is to split it up and calculate it separately and then merge it at the end.

Best Regards

Lucien

[v-luwang-msft]

Hi @wuxxx711 , 

Try the following steps:

1,create a new date table :

2.create new column on the new table :

Item# = MAX('Table'[Item#])

3. use the following measure:

Status2 = 
VAR test1 =
    CALCULATE (
        MAX ( 'Table'[Status] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Updated date] = MAX ( Update_date[Updated date] )
        )
    )
VAR n = 1
VAR test2 =
    IF (
        CALCULATE (
            MAX ( 'Table'[Status] ),
            FILTER (
                ALL ( 'Table' ),
                DATE ( YEAR ( 'Table'[Updated date] ), MONTH ( 'Table'[Updated date] ) + n, DAY ( 'Table'[Updated date] ) )
                    = MAX ( Update_date[Updated date] )
            )
        )
            > BLANK (),
        CALCULATE (
            MAX ( 'Table'[Status] ),
            FILTER (
                ALL ( 'Table' ),
                DATE ( YEAR ( 'Table'[Updated date] ), MONTH ( 'Table'[Updated date] ) + N, DAY ( 'Table'[Updated date] ) )
                    = MAX ( Update_date[Updated date] )
            )
        ),
        CALCULATE (
            MAX ( 'Table'[Status] ),
            FILTER (
                ALL ( 'Table' ),
                DATE ( YEAR ( 'Table'[Updated date] ), MONTH ( 'Table'[Updated date] ) + N + 1, DAY ( 'Table'[Updated date] ) )
                    = MAX ( Update_date[Updated date] )
            )
        )
    )





VAR m=3
VAR test5 =
    IF (
        CALCULATE (
            MAX ( 'Table'[Status] ),
            FILTER (
                ALL ( 'Table' ),
                DATE ( YEAR ( 'Table'[Updated date] ), MONTH ( 'Table'[Updated date] ) + m, DAY ( 'Table'[Updated date] ) )
                    = MAX ( Update_date[Updated date] )
            )
        )
            > BLANK (),
        CALCULATE (
            MAX ( 'Table'[Status] ),
            FILTER (
                ALL ( 'Table' ),
                DATE ( YEAR ( 'Table'[Updated date] ), MONTH ( 'Table'[Updated date] ) + m, DAY ( 'Table'[Updated date] ) )
                    = MAX ( Update_date[Updated date] )
            )
        ),
        CALCULATE (
            MAX ( 'Table'[Status] ),
            FILTER (
                ALL ( 'Table' ),
                DATE ( YEAR ( 'Table'[Updated date] ), MONTH ( 'Table'[Updated date] ) + m + 1, DAY ( 'Table'[Updated date] ) )
                    = MAX ( Update_date[Updated date] )
            )
        )
    )



VAR test4 =
    IF ( test1 <> BLANK (), test1, if(test2<>blank(),test2,test5) )
RETURN
    test4

If there are still blank values returned, you can add the loop section a few more times:

VAR m=3
VAR test5 =
    IF (
        CALCULATE (
            MAX ( 'Table'[Status] ),
            FILTER (
                ALL ( 'Table' ),
                DATE ( YEAR ( 'Table'[Updated date] ), MONTH ( 'Table'[Updated date] ) + m, DAY ( 'Table'[Updated date] ) )
                    = MAX ( Update_date[Updated date] )
            )
        )
            > BLANK (),
        CALCULATE (
            MAX ( 'Table'[Status] ),
            FILTER (
                ALL ( 'Table' ),
                DATE ( YEAR ( 'Table'[Updated date] ), MONTH ( 'Table'[Updated date] ) + m, DAY ( 'Table'[Updated date] ) )
                    = MAX ( Update_date[Updated date] )
            )
        ),
        CALCULATE (
            MAX ( 'Table'[Status] ),
            FILTER (
                ALL ( 'Table' ),
                DATE ( YEAR ( 'Table'[Updated date] ), MONTH ( 'Table'[Updated date] ) + m + 1, DAY ( 'Table'[Updated date] ) )
                    = MAX ( Update_date[Updated date] )
            )
        )
    )

Then base on status ,new column:

and final :

You could download my pbix file if you need. 

Wish it is helpful for you !

Best Regards

Lucien

[wuxxx711]

Thanks for your answer, it looks good. I just added a new item# B456 in my example. In this case, how can we copy a new row for B456? Thanks!