cancel
Showing results for
Did you mean:
Frequent Visitor

## Dax help needed for latest 8 weeks total

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!

1 ACCEPTED SOLUTION

Accepted Solutions
Super User

## Re: Dax help needed for latest 8 weeks total

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

Regards

MFelix

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

2 REPLIES 2
Super User

## Re: Dax help needed for latest 8 weeks total

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

Regards

MFelix

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Highlighted
Frequent Visitor

## Re: Dax help needed for latest 8 weeks total

Hi MFelix,

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

Announcements

#### New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

#### Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

#### Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors (Last 30 Days)