cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
schwinnen Member
Member

SUM of value for current week

I want to calculate the SUM of 'Valid Production Shipments' for the current week.  I will then need to figure out the SUM of 'Valid Production Shipments' for the previous week, but I believe I can figure that out once I figure out the current week.  

I am using the following formula:

 

Weekly Prod Shipments =
CALCULATE (
SUM ('Prod Ops'[Valid Production Shipment] ),
FILTER(ALL('Prod Ops'),'Prod Ops'[Prod Ops WEEK NUMBER]
= min ('Prod Ops'[Prod Ops WEEK NUMBER])))
 
Where am I going wrong?
1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: SUM of value for current week

hi, @schwinnen

 Is "current week" the weeknum  of today?

You could try this formula as below:

First, you need to add a year number column

Year Number = YEAR('Prod Ops'[QA Prod.origin_earliest_arrival_et])

If there are two or more years in the dataset, this will lead to multiple same Week Number

 

Second, try this formula

New Weekly Prod Shipments = 
CALCULATE (
SUM ('Prod Ops'[Valid Production Shipment] ),
FILTER(ALL('Prod Ops'[Week Number],'Prod Ops'[Year Number]),'Prod Ops'[Week Number]
= WEEKNUM(TODAY())&&'Prod Ops'[Year Number]=YEAR(TODAY())))

Result:

2.JPG

 

Best Regards,

Lin

 

 

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

4 REPLIES 4
Super User
Super User

Re: SUM of value for current week

Hi @schwinnen

Are you sure you're not eliminating other filters you'd want active by using ALL('Prod Ops') instead of ALL('Prod Ops'[Prod Ops WEEK NUMBER])? What about the year?

What is the setup Where are you using this measure? (I am assuming it is a measure and not a calculated column)

schwinnen Member
Member

Re: SUM of value for current week

Weekly Prod Shipments is a measure.  

Valid Production shipments is a calculated column in my data.  There are rows and rows of shipments in my data.  If a shipment is valid, it is given a value of 1 and if it is not valid, it is given a value of 0.  So my measure is attempting to sum the valid shipments in the current week.  

I have a column called origin_earliest_arrival_et, which is the exact date and time the route is scheduled to begin.

My week number column is:
Week Number = WEEKNUM('Prod Ops'[QA Prod.origin_earliest_arrival_et])

So I need a sum of all Valid Production Shipments within the current Week Number.

 

As an example, I created a similar measure for daily shipments and it seems to be working perfectly.  So, it is just a matter of translating the below formula to calculate current week instead of current day.

 

Daily Prod Shipments =
CALCULATE (
SUM ('Prod Ops'[Valid Production Shipment] ),
FILTER(ALL('Prod Ops'[QA Prod.origin_earliest_arrival_et].[Date]), 'Prod Ops'[QA Prod.origin_earliest_arrival_et].[Date]
= TODAY()))
Community Support Team
Community Support Team

Re: SUM of value for current week

hi, @schwinnen

 Is "current week" the weeknum  of today?

You could try this formula as below:

First, you need to add a year number column

Year Number = YEAR('Prod Ops'[QA Prod.origin_earliest_arrival_et])

If there are two or more years in the dataset, this will lead to multiple same Week Number

 

Second, try this formula

New Weekly Prod Shipments = 
CALCULATE (
SUM ('Prod Ops'[Valid Production Shipment] ),
FILTER(ALL('Prod Ops'[Week Number],'Prod Ops'[Year Number]),'Prod Ops'[Week Number]
= WEEKNUM(TODAY())&&'Prod Ops'[Year Number]=YEAR(TODAY())))

Result:

2.JPG

 

Best Regards,

Lin

 

 

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

schwinnen Member
Member

Re: SUM of value for current week

@v-lili6-msft, this does seem to be the correct solution.  Thank you for your help.

Helpful resources

Announcements
Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 115 members 1,393 guests
Please welcome our newest community members: