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 IX
Super User IX

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 IX
Super User IX

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 IX
Super User IX

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 IX
Super User IX

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

Find your favorite faces from the community presenting at the Power Platform Community Conference!

Upcoming Events

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors