Cumulative total for "selected week"

DAX DAX Calculations
1

Hello, I want to show cumulative total for selected week only. My “selected week” measure is working fine. It shows the selected week number if any week is selected, else the current week as default.

Please let me know where am i going wrong with this.

Cumulative Sales = CALCULATE([Total Sales],
                                    FILTER(CALCULATETABLE('Calendar','Calendar'[Sales Week Number] = [Selected Week]), 
                                       'Calendar'[Day of Week] <= MAX('Calendar'[Day of Week])))

Calendar table looks like and connect with Sales table using Day column

Day | Day of Week | Week Number | Year

31/12/2017 | 07 | 52 |2017

Thanks for the help.

2

Any reason why you can’t just use the standard cumulative pattern here,

CumulativeTotal = CALCULATE( [Total Sales],
                        FILTER( ALLSELECTED( Dates ), Dates[Date] <= MAX( Dates[Date] ) ) )

The CALCULATETABLE part just seems a little unnecessary for what you’re trying to do.

If you have a week selected that the natural context will filter this formula correctly and give you the correct cumulative total. Well, it should do.

Let me know if your scenario is different to this and add some images if you can.

Thanks

3

Hello, Thanks for the reply. I feel like I am missing something very basic here when calculating comparison values. Could you clarify the difference between these two formulas.

(1) - This gives me correct visual :
Sales Previous Week = CALCULATE([Total Sales],DATEADD('Calendar'[DAY],-7,DAY)

correct%20pic

(2) This give me constant figure (which is total sales number) against all three weeks
Sales Previous Week = CALCULATE([Total Sales],'Calendar'[Sales Week Number]-1)

incorrect

4

Yes number 2 is just not possible.

You need to start using FILTER.

Here’s some good examples.

1 Like
5

Hi Sam,

Watched the video. Pretty good. It works perfectly. I added one more field in the visual to check if it works. But it works perfectly.

Will it be too much to ask to explain how it works behind the scenes. From my example below, how does it pick the numbers correctly for the same day last week and for the same product. How does it account the for the product when i haven’t mentioned that in my formula ??

Capture%201

capture%202

formula

Thanks heaps !

6

A think a good review of context is probably needed here. That is what is enabling the correct filtering.

Here’s a few view to review from the mastering DAX course module. There are crucial concepts to master.

Start with this one and work your way down

http://portal.enterprisedna.co/courses/mastering-dax-calculations/lectures/2000594

7

Hi @sam.mckay,

If I may kindly re-open this issue, kindly advise how will the DAX be in combining both the subjects of cummulative total and finding the cummulative total in the previous week (non-standard date range). Basically, I’d like to have & compare the current week cumulative amount/total & the previous week cummulative amount/total.

KRgds, -Nik