Need Cumulative Sum by Item

[sandeepk66]

Hi Geeks,
Need Cumulative Sum of the AMOUNTper each Item.
I have created a column ' Cumulative Sum' in Excel, which is basically AMOUNT+Qty-Liability  per Item.

needed to replicate same in PowerBIDesktop,

Any Ideas would be appreciated.

Link to Dashboard pbix

Excel Data

[Phil_Seamark]

Hi @sandeepk66

Just checking.  In the first line of each of your product groupings, you use three column to determine the result, then from that result, the next lines only use 2 columns.  Is that what you meant to do, or was that a typo?

[Phil_Seamark · Accepted solution]

Hi Again @sandeepk66

In anycase, here is a calculated column that might work

Column = 
VAR StartRowAmount = MINX(FILTER(Examples,'Examples'[ITEM] = EARLIER('Examples'[ITEM])),'Examples'[AMOUNT])
RETURN 
    CALCULATE(
                        StartRowAmount + 
                        SUM([SALE Amount]) - 
                        SUM([Liability])
                        , FILTER(
                        ALL('Examples'),
                        'Examples'[RowID] <= EARLIER('Examples'[RowID]) 
                        && 'Examples'[ITEM] = EARLIER('Examples'[ITEM]))
                        )

[sandeepk66]

Hi @Phil_Seamark

Appreciated for your work, its working for that example I Provided.

However, its not working for this scenario. Please find the EXCEL and pbix.

Thank you!

Here is the Data

Here is the pbix file

[Phil_Seamark]

HI @sandeepk66

Try adding this code as a calculated column to your Query1 table, rather than to your Examples table.

Also, I note the data in your INVENTTRANSID column is not in order.  Is that important?

CUMColumn2 = 
VAR StartRowAmount = MINX(
                        FILTER(Query1,'Query1'[NAME] =EARLIER('Query1'[NAME])),'Query1'[PHYSICALINVENT])
RETURN 
    CALCULATE(
                        StartRowAmount + 
                        SUM(Query1[RECEIPTQTY]) - 
                       sum(Query1[ISSUEQTY])
                        , FILTER(
                        ALL('Query1'),
                        'Query1'[INVENTTRANSID] <= EARLIER('Query1'[INVENTTRANSID])
                        && 'Query1'[NAME] = EARLIER('Query1'[NAME]))
                        )

[sandeepk66]

Hi @Phil_Seamark,

Appreciated your inputs.

However,Its not working for the same NAME for that day.

Link to pbix
Please see the Issue that I ran into:
Issue example 1
Issue example 2

Can we do this Cumulative Sum by Dimension in Power Query?