CURRENT WEEK & FUTURE WEEKS CALCULATION

[Julius410]

Hi there,

I am looking for a solution to calculate inventory projections. I have a matrix that contains the following colums for the current week (ending Sunday).

Columns here is Week Ending. For example, 17/12/2023 and values are:

Item Code	Stock On Hand	Production	Demand	Balance	Weeks Cover
Specifies the code of the item	Amount of stock that is currently on hand	Amount of stock that should be produced until the end of this week	Amount of stock that customers requested until the end of this week	(Stock On Hand + Production) - Demand	(Stock On Hand + Production) / Demand

I have another matrix that contains th columns for the next week (e.g., 24/12/2023) and up to 12 weeks in the future. In this matrix, I would like to calculate my inventory projections and it should look like this:

Item Code	Stock On Hand	Production	Demand	Balance	Weeks Cover
Specifies the code of the item	Balance of Previous Week	Amount of stock that should be produced until the end of this week	Amount of stock that customers requested until the end of this week	(Stock On Hand + Production) - Demand	(Stock On Hand + Production) / Demand

I am looking for a calculation that allows me to specify the current week and the previous week. So far, I have used this calculation:

week number = WEEKNUM(Table[Date])

current week number = IF(WEEKNUM(Table[Date])=WEEKNUM(TODAY()),1,0)

This formula does not work correctly because for the current ending week (17/12/2023) it gives me two values: 1 and 0.

Would anyone know how to solve this?

Many thanks.

Julius

[123abc]

The issue you're facing with the calculation of the current week might be due to the fact that the current week could span across two different years.

Here's a modified version of your formula that takes this into account:

WeekNumber = WEEKNUM(Table[Date], 2)
CurrentWeekNumber = IF(WeekNumber = WEEKNUM(TODAY(), 2), 1, 0)

It seems like you are trying to calculate inventory projections in Power BI based on a matrix with information about the current week and future weeks. The issue you're facing with the calculation of the current week might be due to the fact that the current week could span across two different years.

Here's a modified version of your formula that takes this into account:

DAXCopy code

WeekNumber = WEEKNUM(Table[Date], 2) CurrentWeekNumber = IF(WeekNumber = WEEKNUM(TODAY(), 2), 1, 0)

The second argument (2) in the WEEKNUM function specifies the system to use ISO 8601 week numbering, which is widely used in business and starts counting from Monday. This helps to avoid issues related to week numbering across different years.

Now, this formula should correctly identify the current week as 1 and others as 0.

To calculate inventory projections for the future weeks, you can use DAX formulas similar to the ones you provided. Assuming you have a table named "Inventory" and the columns "Item Code," "Stock On Hand," "Production," "Demand," "Balance," and "Weeks Cover," you can create calculated columns like this:

BalanceFutureWeek =
CALCULATE(
[Balance],
FILTER(
ALL(Inventory),
Inventory[WeekNumber] > EARLIER(Inventory[WeekNumber])
)
)

WeeksCoverFutureWeek =
CALCULATE(
[Weeks Cover],
FILTER(
ALL(Inventory),
Inventory[WeekNumber] > EARLIER(Inventory[WeekNumber])
)
)

These formulas calculate the Balance and Weeks Cover for future weeks based on the Balance of the previous week. Make sure to adjust the column and table names according to your actual data model.

Once you have these calculated columns, you can use them in your Power BI reports to visualize the inventory projections for the current and future weeks.

[Julius410]

Thank you very much for your detailed answer.

The calcuation for the current week worked. However, the future balances and weeks cover give me the following error "The Column... cannot be pushed to the remote data source and cannot be used in this scenario."

I am using a table visual now and calculate the columns seperately which works as well.

Thanks again.