cancel
Showing results for
Did you mean:
Highlighted
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:

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
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]))``````

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

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

Proud to be a Super User!

6 REPLIES 6
Highlighted
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.

Proud to be a Super User!

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

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

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

Proud to be a Super User!

Highlighted
Continued Contributor

@summer18

Highlighted
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

## Re: sum of value by specific week numbers

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

## Re: sum of value by specific week numbers

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

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

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

Proud to be a Super User!

Announcements

#### Power Platform Community Conference

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

#### Experience what’s next for Power BI

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

#### Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021