Need a calculation to take first week's value when rolled up to month

[marksaba]

Hello,

I have a "Starting Inventory" metric that is available at a Weekly level in my data set. At a weekly level, I am seeing the information for Starting Inventory correctly (9/19/22: 3,498, 9/26/22: 6,723). 

However, when I collapse the weeks to show the data by month, PowerBI naturally sums the Starting Inventory from the week level. Instead, I would like a calculation that takes the first result by week when rolled up into months. As shown below, when rolled up to months, Starting Inventory equals 10,221 (3498+6723). I would instead like the result to be 3,498, as that is the first week available under September.  Does anyone know of a solution/calculation to make this happen?

Additional details. I have tried this DAX but am forced to input "VALUES" as the system will not let me do YEAR('2-Production and Sales Inventory'[month]), despite the fact that [month] is in date/time format, which should allow it to work per this article. I don't know why the column is not appearing in the open parantheses after YEAR. 

Thank you!! 

[v-xiaotang]

Hi @marksaba 

Thanks for reaching out to us.

could you share a sample .pbix file? thanks.

Best Regards,

Community Support Team _Tang

If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

[lbendlin]

 when I collapse the weeks to show the data by month,

Weeks and Months are incompatible.

Anyway, Please provide sanitized sample data that fully covers your issue.
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...
Please show the expected outcome based on the sample data you provided.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523