Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
JacobLI
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
MFelix
Super User
Super User

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

Miguel Félix


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

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

2 REPLIES 2
MFelix
Super User
Super User

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

Miguel Félix


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

Proud to be a Super User!

Check out my blog: Power BI em Português



Hi MFelix, 

 

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.