Cumulative Running Total by week in a Matrix

[alforc1]

Good Day All,

I am looking for some support with some DAX that I am struggling to piece together.  I am looking within a matrix to sum together each weeks sales & and each weeks sales to the previous weeks sales.  I have built a matrix as displayed below.

From the table I have included the following DAX.

Volume BU Rolling FYTD =
CALCULATE(sum(EPOS[Volume BU]),
            FILTER(ALL('Date'[Week End Date]),
'Date'[Week End Date] <= MAX('Date'[Week End Date])
                     )
)

This works in that it gives me the sum of each week but I need to have a rolling cumulative sum for each week.

For Example Week1 = £2000, Week 2 = £1000, so the cumlative for Week 2 would be £3000. & so on to the end of the year to date.

I am guessing that this should be relatively straightforwards but I have not been able to resolve from my limited DAX knowledge.

I have tried various examples in the forums and sum of the pre defined calculations in quick measures but with no success.

Can anyone advise me on how to achieve this.

Thanks

alforc1

[v-shex-msft · Accepted solution]

HI @alforc1,

If you want to get YTD cumulative running total, I think you need to add a year condition to control formula work on same year range.

Volume BU Rolling FYTD =
CALCULATE (
    SUM ( EPOS[Volume BU] ),
    FILTER (
        ALLSELECTED ( 'Date'[Week End Date] ),
        YEAR ( 'Date'[Week End Date] ) = YEAR ( MAX ( 'Date'[Week End Date] ) )
            && 'Date'[Week End Date] <= MAX ( 'Date'[Week End Date] )
    )
)

Regards,

Xiaoxin Sheng

[strongCoreZach]

Cumulative Volume = 
CALCULATE (
    SUM ( EPOS[Volume BU]),
    FILTER (
        ALL ( 'Date'[Date] ),
        'Date'[Date] <= MAX ( 'EPOS'[Week End Date] )
    )
) 

Make sure in the table/matrix you use the week from the Date table - and make sure your date table is marked as a date table.

[v-shex-msft · Accepted solution]

HI @alforc1,

If you want to get YTD cumulative running total, I think you need to add a year condition to control formula work on same year range.

Volume BU Rolling FYTD =
CALCULATE (
    SUM ( EPOS[Volume BU] ),
    FILTER (
        ALLSELECTED ( 'Date'[Week End Date] ),
        YEAR ( 'Date'[Week End Date] ) = YEAR ( MAX ( 'Date'[Week End Date] ) )
            && 'Date'[Week End Date] <= MAX ( 'Date'[Week End Date] )
    )
)

Regards,

Xiaoxin Sheng

[alforc1]

That has worked perfectly.  You have saved my day.

Thank you for answering Xiaoxin Sheng.

Regards

Chris

[alforc1 · Accepted solution]

Hi, I have a further question in relation to the the previous. The dax works up until the end of the current year but I need the cumulative total to also run into the next year.  can you advise how I would amend the DAX below to roll over into the following year.  So at the end of 2017 the cumulative resets but I would like it to continue into 2018.

Any help would be appreciated.

Volume BU Rolling FYTD =
CALCULATE (
    SUM ( EPOS[Volume BU] ),
    FILTER (
        ALLSELECTED ( EPOS[Week End Date]),
        YEAR ( EPOS[Week End Date] ) = YEAR ( MAX ( EPOS[Week End Date] ) )
            && EPOS[Week End Date] <= MAX ( EPOS[Week End Date] )
    )

Regards

Chris

)