Need a calculation to take first week's value when rolled up to month
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!!Calculate result for next 4, next 10 and next 16 weeks based on month from matrix row
Based on the first column of the matrix (year and month hierarchy), i would like to create a measure that perform a calculation based on another measure. The second column "Calculation" is fine as it receives the date input from the date hierarchy. However, i struggle to perform the calculation for "Next 4" weeks (and also for "Next 10", "Next 16", etc.) The calculation "Next 4" is supposed to take the first date of the month, return the following 4 weeks (or 28 days), and evaluate another measure for the set of dates returned. In example, for january 2020, I want it to perform the calculation for the range between January 1st and January 28th. Similarly, the calculation "Next 10" is supposed to take the first date of the month, return the following 10 weeks (or 70 days), and evaluate another measure for the set of dates returned. In example, for january 2020, I want it to perform the calculation for the range between January 1st and March 11th. "Estimated revenue" is calculated in another measure, which works fine for the monthly date ranges provided by the table. Planned next 4 weeks (dynamic) = // Calculating estimated revenue for the next 28 days from the start of a given month. CALCULATE( [Estimated revenue]; DATESINPERIOD( Revenue[Date]; STARTOFMONTH(DateTable[Date]); 28; DAY ) )
