SUM of value for current week

[schwinnen]

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?

[AlB]

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]

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

[v-lili6-msft · Accepted solution]

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:

Best Regards,

Lin

[schwinnen]

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