Dax help needed for latest 8 weeks total

[JacobLI]

Hi Everyone, I'm really new to DAX and have been struggling to come up with a solution to sum up latest 8 weeks data for each row. I've been trying filter function but it doesn't work. 

I've simplified my ataset below, it has 3 columns:

Week ending date | 8 weeks ago date | count
10/03/2017  13/01/2017     0
17/03/2017  20/01/2017     1
24/03/2017  27/01/2017     1
31/03/2017  3/02/2017       0
7/04/2017    10/02/2017     1
14/04/2017  17/02/2017     0
21/04/2017  24/02/2017     0
28/04/2017  3/03/2017       0
5/05/2017    10/03/2017     1
12/05/2017  17/03/2017     0
19/05/2017  24/03/2017     1
26/05/2017  31/03/2017     0
2/06/2017    7/04/2017       1
9/06/2017   14/04/2017      1
 

What I want is to have a measure that can sum the count for last 8 weeks for each row.

i.e. for 9/06/2017 0:00 i should have get sum of counts from 21/04/2017 0:00 to 9/06/2017 0:00, which is 4. Kindly let me know if any functions or calculations that i could try out, thanks in advance!

[MFelix · Accepted solution]

Hi @JacobLI,

Try this formula

8Weeks_Sum =
            CALCULATE (
                SUM ( [count] ),
                FILTER (
                    ALL ( Table1 ),
                    Table1[Week ending date] <= MAX ( Table1[Week ending date] )
                        && Table1[Week ending date] > MAX ( Table1[8 weeks ago date] )
                )
            )

Final result below within the formula you can see the ALL formula that will always calculate the last 8 weeks even if the table is filter in my image I show the image with slicer and without slicer so you can see the result is always the same.

Don't know if you need to have only the result for the filtered rows if you add a slicer if so you need to change the formula to the one below:

8WeeksSum = 

CALCULATE(SUM([count]),
                       FILTER(ALLSELECTED(Table1)
                                  ,Table1[Week ending date] <= MAX(Table1[Week ending date]) && Table1[Week ending date] > MAX(Table1[8 weeks ago date]) 
                                  )
                     )

 This will change the values to to ALLSELECTED.

Regards,

MFelix

[JacobLI]

Hi MFelix, 

Thanks so much for your fast response, and I have just tried out your two methods, both works well!