Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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:
Solved! Go to 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
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)
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.
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.
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
@v-lili6-msft, this does seem to be the correct solution. Thank you for your help.