Cumulative sum on date AND per item

DAX DAX Calculations
1

Good evening,
may i please ask for your help, i am struggling following issue, i would like to create the cumulative sum in an order table, based on date and per item as a calculated column.
please find sample file annexed.

2020-03-01_21h38_48
2020-03-01_21h38_48721Ă—285 43.6 KB

thanks a lot, kind regards
Franz

helpfile010320.pbix (60.6 KB)

2

Hi @Franz,

Here you go. Thanks for providing sample pbix file. I have added date table and following is your required formula to calculate results.

Cummulative Total =

VAR currentitem =
    VALUES ( 'data'[Item] )
RETURN
    IF (
        HASONEVALUE ( 'data'[Item] ),
        CALCULATE (
            SUM ( 'data'[Quantity] ),
            FILTER (
                ALLSELECTED ( 'data' ),
                'data'[Date] <= MAX ( 'data'[Date] )
                    && 'data'[Item] = currentitem
            )
        ),
        CALCULATE (
            SUM ( 'data'[Quantity] ),
            FILTER ( ALLSELECTED ( 'data'[Date] ), 'data'[Date] <= MAX ( 'data'[Date] ) )
        )
    )

image

Enterprise DNA - Cummulative total issue.pbix (112.6 KB)

2 Likes
3

Hi Hafiz Sultan,

it works perfect as a measure, please can i have this formula as a “calculated column”, i need to evaluate line by in line in my order table against available stock

thanks in advance, kind regards
Franz

4

Hi @Franz,

Here you go:

image

Desired Result(CC) =
SUMX (
    FILTER (
        'desired result',
        'desired result'[Date] <= EARLIER ( 'desired result'[Date] )
            && 'desired result'[Item] = EARLIER ( 'desired result'[Item] )
    ),
    'desired result'[Quantity]
)

Enterprise DNA - Cummulative total issue-CC.pbix (118.6 KB)

2 Likes
5

@hafizsultan,

Very nice solution. Plus extra degree of difficulty points for the use of EARLIER - IMO the runaway winner of the Most Confusing DAX Function award.

  • Brian
1 Like
6

Hi @BrianJ,

Thanks, yes indeed, EARLIER is very confusing in the start and I believe one can only understand it after using it for sometime :slight_smile:

7

@hafizsultan,

I just use a VAR to capture the prior row context, and steer clear of it entirely.

  • Brian
1 Like
8

Thank you very much, works perfect :grinning:

1 Like
9

Hi @Franz,

Glad that it worked for you. Please mark it as solved. Cheers!

10

Hi @Franz, I hope that you are having a great experience using the Support Forum so far. We’ve recently launched the Enterprise DNA Forum User Experience Survey, please feel free to answer it and give your insights on how we can further improve the Support forum. Thanks!