Forum Discussion

S_JB's avatar
S_JB
Resolver III
5 years ago
Solved

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 f...
Need Help
  • S_JB's avatar
    S_JB
    5 years ago

    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 avatar
amitchandak
Super User
5 years ago

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