Forum Discussion
Dynamic Field for Quantity
Hi all,
I'm trying to build an inventory report in Power BI to replace a report we have in excel.
The below example shows how the report currently looks in excel:
The inventory column for week 1 is populated using the inventory for the item in the system as of today. Moving forwards, the inventory is calculated for week using the following sum: Inventory = (Inventory + Ordered) - Sold
Ideally, I'd like to build a matrix which has item number for the rows, week number for the columns and the inventory quantities as the values. However, I'm really unsure how I could build this dynamically in Power BI. A DAX formula replicating the above works for the current week, but the inventory will disappear for any other weeks as the inventory is a static field in the system.
Please can anyone advise?
Thanks!
Thanks for your assistance but unfortunately this approach would not work for what I was trying to achieve.
I had to create a measure for week 1 quantity using the historic data and current week, then use this measure as a basis to create a rolling 8 week view.
2 Replies
S_JB , if you have date , use the date table with the week.
and use a formula like
[Intial Inventory] + CALCULATE(SUM(Table[Ordered]),filter(date,date[date] <=maxx(date,date[date]))) - CALCULATE(SUM(Table[Sold]),filter(date,date[date] <=maxx(date,date[date])))
Week column in in Date Table
Week Start date = 'Date'[Date]+-1*WEEKDAY('Date'[Date],2)+1
Week End date = 'Date'[Date]+ 7-1*WEEKDAY('Date'[Date],2)
Week Number = WEEKNUM([Date],2)
Week = if('Date'[Week Number]<10,'Date'[Year]*10 & 'Date'[Week Number],'Date'[Year]&'Date'[Week Number])
Week Rank = RANKX(all('Date'),'Date'[Week Start date],,ASC,Dense)
Week name = [Week Start date] & " to "& [Week End date]
Weekday = WEEKDAY([Date],2)
WeekDay Name = FORMAT([Date],"ddd")Thanks for your assistance but unfortunately this approach would not work for what I was trying to achieve.
I had to create a measure for week 1 quantity using the historic data and current week, then use this measure as a basis to create a rolling 8 week view.
