Show only last week ending inventory

[Kenta]

Hi,

I receive files weekly from dealers that include sales and inventory which I consolidate/append to all previous data they've sent me. I would like to have a column in my table that shows inventory levels for only the last week of the selected period.

Example: If I select multiple weeks with my slicer, the "Total Inventory" column which is currently a sumx measure of all dealer files will sum all weekly inventory levels for the selected period which creates a grossely overstated inventory picture.

[amitchandak · Accepted solution]

@Kenta , Try

calculate(lastnonblankvalue(Date[Week End Date], [Total Inv]))

or have week rank on week start date or week end date in a date table
Week Rank = RANKX(all('Date'),'Date'[Week Start date],,ASC,Dense)
OR
Week Rank = RANKX(all('Date'),'Date'[Year Week],,ASC,Dense) //YYYYWW format

Then try measures like
This Week = CALCULATE([Total Inv], FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])))
Last Week = CALCULATE([Total Inv], FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])-1))