cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper II
Helper II

sum of value by specific week numbers

Hi, 

 

I need your help on getting the sum of specific week start and week end.  I tried running total but it's not the expected result.

My chart should look like this whether in table or bar chart:

 

sample01.png

 

It's easy to do this in Excel but could not get it correctly in PowerBI.  Please refer to the details below:

 

sample02.png

 

Here's the link of sample pbix.  

https://drive.google.com/file/d/1KRJfdfqbV88I1Y5eKE2eCvwZSMunhtdr/view?usp=sharing

 

Thanks in advance for your help and sharing your knowledge.

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User IV
Super User IV

Re: sum of value by specific week numbers

@summer18 - Seems like this should be:

 

Measure =
  VAR __WeekMax = MAX('Table'[Week])
  VAR __WeekMin = __WeekMax - 6
  VAR __Table = FILTER(ALL('Table'),[Week] >= __WeekMin && [Week] <= __WeekMax)
RETURN
  IF(__WeekMin <= 0,BLANK(),SUMX(__Table,[Expenses]))

---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




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

Proud to be a Super User!




View solution in original post

6 REPLIES 6
Highlighted
Super User IV
Super User IV

Re: sum of value by specific week numbers

@summer18 , refer if my blog on week can help. You can create a week year table if you do not have date. and use that.

 

https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-La...

 

Week of choice -https://community.powerbi.com/t5/Community-Blog/Any-Weekday-Week-Decoding-Date-and-Calendar-2-5-Powe...



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Highlighted
Super User IV
Super User IV

Re: sum of value by specific week numbers

@summer18 - Seems like this should be:

 

Measure =
  VAR __WeekMax = MAX('Table'[Week])
  VAR __WeekMin = __WeekMax - 6
  VAR __Table = FILTER(ALL('Table'),[Week] >= __WeekMin && [Week] <= __WeekMax)
RETURN
  IF(__WeekMin <= 0,BLANK(),SUMX(__Table,[Expenses]))

---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




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

Proud to be a Super User!




View solution in original post

Highlighted
Continued Contributor
Continued Contributor

Re: sum of value by specific week numbers

@summer18 
The PBIX file link requires a google sign-in open - please change settings in google to allow anyone with the link to download.  

 

This way - we can use your underlying table design to help you.  

Highlighted
Helper II
Helper II

Re: sum of value by specific week numbers

Hi @amitchandak I tried the Week Start Date and Week End Date before posting my topic but I could not get it to work correctly on my scenario. Since you provided me on my previous post your blog links, I always refer to them and they're really helpful. Thanks for that
Highlighted
Helper II
Helper II

Re: sum of value by specific week numbers

Hi @Greg_Deckler ,

Thanks for your help. Your formula worked on my issue.  I just added Year in the formula so that the calculation will still be in the same year

 

Measure =
  VAR __WeekMax = MAX('Table'[Week])
  VAR __WeekMin = __WeekMax - 6
  VAR __Table = FILTER(ALL('Table'),

                                  'Table'[Year] = MAX( 'Table'[Year]) &&

[Week] >= __WeekMin && [Week] <= __WeekMax)
RETURN
  IF(__WeekMin <= 0,BLANK(),SUMX(__Table,[Expenses]))
 

Highlighted
Super User IV
Super User IV

Re: sum of value by specific week numbers

@summer18 - Awesome! Glad to hear it. Yes, good call on the year!


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




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

Proud to be a Super User!




Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Tech Marathon

Maratón de Soluciones de Negocio Microsoft

Una semana de contenido con +100 sesiones educativas, consultorios, +10 workshops Premium, Hackaton, EXPO, Networking Hall y mucho más!

Top Solution Authors
Top Kudoed Authors