cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
JacobLI Frequent Visitor
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
Highlighted
Super User
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.

 

8weekscalc.png

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!




View solution in original post

2 REPLIES 2
Highlighted
Super User
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.

 

8weekscalc.png

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!




View solution in original post

JacobLI Frequent Visitor
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! 

Helpful resources

Announcements
New Topics Started Badges Coming

New Topics Started Badges Coming

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

MBAS 2020

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

Difinity Conference

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

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

Top Solution Authors
Top Kudoed Authors (Last 30 Days)