Calculating Cumulative Total with the most updated values

[hokitkan]

Hi everyone, I'm new to Power BI and now I'm facing a problem about the Cumulative Total.

The below table shows the information about number of products at certain timepoints sort by Product Type. The number of products may fluctuate due to products sold or get inventory replenishment.

 

What I want to do is to calculate the Cumulative Total chronologically so I can obtain a output table as follow.

I found informations about how to calculate cumulative total by group using the CALCULATE(SUM(), FILTER(ALL(),
'Table'[Date] <= MAX('Table'[Date]))) Syntax. However, I cant figure out how to calculate the cumulative total when same type of products occur repeatedly with an updated numbers. How to ignore the old value and use the latest value encounter at the corresponding timepoints?

I cant find any useful information on the web. So any hints or advises would be much appericated!

[hymieho]

Hi, I'm not exactly sure but I think you could RANK Type by Time and only SUM the top 1 (the latest value). 

[hokitkan]

Thanks for your reply! I think your advise can help me to obtain the final number of products at the end of the day. But I also want to get the data throughout the day, so I can know how many products are in the store at each time point. 

Actually I am not sure if power bi desktop can really deal with this situation. As I know power bi desktop is working on column level but my problem is on cell level (once encounter the repeated types of product, ignore the value use before and do calculation with the new value). 

What do you think? I would appreciate any ideas!

[Vvelarde · Accepted solution]

@hokitkan

Hi, please try with this measure:

cumulative =
VAR vtime =
    MAX ( Table1[TIME] )
RETURN
    CALCULATE (
        SUMX (
            SUMMARIZE (
                Table1,
                Table1[REGION],
                Table1[TYPE],
                "MXamount", SELECTCOLUMNS ( TOPN ( 1, Table1, Table1[TIME] ), "AM", Table1[AMOUNT] )
            ),
            [MXamount]
        ),
        FILTER ( ALLEXCEPT ( Table1, Table1[REGION] ), Table1[TIME] <= vtime )
    )

let me know if works

Victor

Lima-Peru

[hymieho]

Yes, thanks. And now I know about SELECTCOLUMNS, and I had not seen this pattern for adding individual columns back after summarizing--I have only seen calculated columns.  Great stuff!

[hokitkan]

It works perfectly!

Really impressed by the logic and thanks for your help!