cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

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.

 

Capture.PNG

 

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

 

2 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted
Community Support
Community Support

Re: Cumulative Running Total by week in a Matrix

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

Highlighted
Frequent Visitor

Re: Cumulative Running Total by week in a Matrix

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


)

View solution in original post

3 REPLIES 3
Highlighted
Community Support
Community Support

Re: Cumulative Running Total by week in a Matrix

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

Highlighted
Frequent Visitor

Re: Cumulative Running Total by week in a Matrix

That has worked perfectly.  You have saved my day.

 

Thank you for answering Xiaoxin Sheng.

 

Regards

 

Chris

Highlighted
Frequent Visitor

Re: Cumulative Running Total by week in a Matrix

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


)

View solution in original post

Helpful resources

Announcements

August Community Highlights

Check out a full recap of the month!

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

Top Solution Authors