Dynamic Field for Quantity

[S_JB]

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!

[S_JB · Accepted solution]

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.

[amitchandak]

@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")