sum of value by specific week numbers

[summer18]

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:

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

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.

[swise001]

@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.  

[Greg_Deckler · Accepted solution]

@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]))

[summer18]

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])) 

[Greg_Deckler]

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

[amitchandak]

@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...

[summer18]

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