Forum Discussion
CURRENT WEEK & FUTURE WEEKS CALCULATION
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:
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.
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.
