Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
wuxxx711
Frequent Visitor

How to copy latest row and calculate cumulative total

Hello,

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

 

Item#StatusOpen dateClosed dateUpdated dateTotal
A123O12/1/2020 12/1/2020$100
A123C12/1/20202/1/20212/1/2021$50
A123RO12/1/2020 5/1/2021$70
B456O2/1/2021 2/1/2021$60
B456C2/1/20214/1/20214/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#StatusOpen dateClosed dateUpdated dateTotal 
A123O12/1/2020 12/1/2020$100 
A123O12/1/2020 1/1/2021$100new
A123C12/1/20202/1/20212/1/2021$150 
A123C12/1/20202/1/20213/1/2021$150new
A123C12/1/20202/1/20214/1/2021$150new
A123RO12/1/2020 5/1/2021$220 
B456O2/1/2021 2/1/2021$60 
B456O2/1/2021 3/1/2021$60new
B456C2/1/20214/1/20214/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 

 

5 REPLIES 5
v-luwang-msft
Community Support
Community Support

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

unfortunately, I have not solved this problem.😂

v-luwang-msft
Community Support
Community Support

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
Community Support
Community Support

Hi @wuxxx711 , 

Try the following steps:

1,create a new date table :

v-luwang-msft_0-1619078006206.png

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:

v-luwang-msft_1-1619078268311.png

and final :

v-luwang-msft_2-1619078289631.png

You could download my pbix file if you need. 

Wish it is helpful for you !

 

Best Regards

Lucien

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! 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.