topic Re: Need a calculation to take first week's value when rolled up to month in DAX Commands and Tips

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

Anonymous — Fri, 30 Sep 2022 18:40:29 GMT

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!!

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

lbendlin — Sat, 01 Oct 2022 23:12:37 GMT

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-p/963216
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

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

v-xiaotang — Mon, 03 Oct 2022 09:25:05 GMT

Hi Anonymous

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.