Forum Discussion

alforc1's avatar
alforc1
Frequent Visitor
8 years ago
Solved

Cumulative Running Total by week in a Matrix

 

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

 

Need Help
  • Anonymous's avatar
    Anonymous
    8 years ago

    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's avatar
    alforc1
    8 years ago

    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


    )

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    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's avatar
      alforc1
      Frequent Visitor
      8 years ago

      That has worked perfectly.  You have saved my day.

       

      Thank you for answering Xiaoxin Sheng.

       

      Regards

       

      Chris

      • alforc1's avatar
        alforc1
        Frequent Visitor
        8 years ago

        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


        )

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago
    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.