Use "last date" as a static filter regardless of slicers - non standard calendar

[lauralovesBI]

Happy friday to everyone!

This seems like an easy DAX measure but I cant seem to work it out. I have a report showing the last week of product stock data (our lowest level of granularity in the non standard calendar). This report has slicers for users based on different product attributes. The visuals are prefiltered to show only data from the last week. But......if the user chooses from the slicers a product that was not in stock the last week, the visual adjusts to the last week that product was available (even if it was 2 years ago).

Question: how do I isolate the last week as a measure?

the calendar measure is currently:

Latest week Absolute = CALCULATE(MAX(stockdata[Week]),ALL(stockdata))

the data measure is:

Stock quantity latest week = CALCULATE(stockdata[stock qty], FILTER(Calendar, [Latest week Absolute]))

The result should be blank if not included in the latest week of stock data. How do I show that instead of reverting to the last week the product was in stock?

Thank you all in advance for the help!

[v-rzhou-msft]

Hi @lauralovesBI ,

Does last week mean the latest week before current one?

What column did you use to relate "Calendar Date" table with "stockdata" table, by [Date] column or [Week] column?

Latest week Absolute = CALCULATE(MAX(stockdata[Week]),ALL(stockdata))

[Latest week Absolute] will return the max week number from "stockdata" table. I think the max number of each year should be the same. I suggest you to add a new column [YearWeek] in your "stockdata" table, format like 202201,...202211.

YearMonth = YEAR(stockdata[Date])*100+WEEKNUM(stockdata[Date])

Then you can get the max YearWeek and Power BI won't be confused by this in calculation.

If this reply still couldn't help you solve your problem, please share a sample file with me. I need to know what does your tables look like. This will make me easier to find solution.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[littlemojopuppy]

Hi @lauralovesBI do you have a date table and is it marked as one?

[lauralovesBI]

hi! yes, i have a star schema with a calendar table (marked as such) and product reference table. Both are connected to the stock data.

[littlemojopuppy]

Hi @lauralovesBI this should be fairly easy then.  Is weeknumber in your date table or part of the product reference table?

[lauralovesBI]

@littlemojopuppy - week number is part of the calendar date table and is connected to the stock data using weeks (in the same format). Product reference table is connected to stock data using product name. Thank you!

[littlemojopuppy · Accepted solution]

Hi @lauralovesBI instead of 

Latest week Absolute = CALCULATE(MAX(stockdata[Week]),ALL(stockdata))

try

Latest week Absolute = MAX(DateTable[Week])

Make sure you substitute the name of your date table and week number field.