DAX last 4 weeks

Step 1: Create a Week Number Column (if not already in Date table

YearWeek key:

YearWeek = 'Date'[Year] * 100 + WEEKNUM('Date'[Date], 2)

Step 2: Find Max Week in Context

MaxWeek =
CALCULATE(
MAX('Date'[YearWeek]),
ALLSELECTED('Date')
)

Step 3: Create a Last 4 Weeks Flag

IsLast4Weeks =VAR _MaxWeek = [MaxWeek]
VAR _MinWeek = _MaxWeek - 3
RETURN
IF('Date'[YearWeek] >= _MinWeek && 'Date'[YearWeek] <= _MaxWeek, 1, 0)

Step 4: Sell-through Measure

SellThrough =
DIVIDE(
SUM(Sales[SalesAmount]),
CALCULATE(
SUM(Stock[StockQty]),
FILTER(
ALL('Date'),
'Date'[IsLast4Weeks] = 1 &&
WEEKDAY('Date'[Date],2) = 1 // take stock only Mondays
)
)
)

Step 5: Visual Setup

Put Date[Week Start] (or YearWeek) on X-axis.

Use SellThrough as Y-axis.

Add visual-level filter → IsLast4Weeks = 1.

Now your line chart will always show last 4 weeks from the latest week selected, even if slicer range is bigger.

could you pls provide some sample data?

Hi ash_best_ 

Thank you for reaching out to the Microsoft Fabric Community Forum.
 

To create the visual that always shows the last 4 weeks of sell-through regardless of the slicer’s minimum date, and to ensure stock is correctly taken from the Monday of each week, please try with the below DAX:

 

MaxWeek =

CALCULATE(

    MAX('Date'[YearWeek]),

    ALL('Date') 
)



IsLast4Weeks =

VAR _MaxWeek = [MaxWeek]

VAR _MinWeek = _MaxWeek - 3

RETURN

IF(

    'Date'[YearWeek] >= _MinWeek &&

    'Date'[YearWeek] <= _MaxWeek,

    1,

    0

)


 

SellThrough =

VAR _Week = MAX('Date'[YearWeek])

VAR _WeekStart = MAX('Date'[WeekStart]) 

VAR _Sales =

    CALCULATE(

        SUM(Sales[SalesAmount]),

        KEEPFILTERS('Date'[YearWeek] = _Week)

    )

VAR _Stock =

    CALCULATE(

        SUM(Stock[StockQty]),

        FILTER(

            ALL('Date'),

            'Date'[Date] = _WeekStart  

        )

    )

RETURN

DIVIDE(_Sales, _Stock)

In the visual, place Date[WeekStart] on the X-axis and use [SellThrough] as the Y-axis. Then, apply a visual-level filter where IsLast4Weeks = 1. With this setup, the chart will always display the last four weeks relative to the maximum week in the slicer. The stock values will be correctly aligned to their Monday snapshot rather than being repeated across all days, and the slicer’s minimum date will no longer cause earlier weeks to disappear.

If this still does not resolve your scenario, kindly share a sample PBIX file with masked or dummy data so we can better understand your data model and assist you in the best possible way.

Regards,
Microsoft Fabric Community Support Team.

Hi ash_best_ 

We have not yet received a response regarding your query. Could you please confirm if your issue has been resolved? If not, kindly provide additional details so we can offer further assistance.

Thank you.

Hi,

I have solved a similar problem in the attached file.

Hi ash_best_ 

To achieve this, you need to decouple your calculation from the slicer by first capturing the latest week in context and then calculating sellthrough for that week and the three weeks prior. You can do this by creating a measure that finds the max week number (or max Monday date) using CALCULATE(MAX('Date'[Week]), ALL('Date')) while still respecting other filters like product or region. Then use this max week to filter your sales and stock data with DATESINPERIOD or FILTER over the past four weeks. Finally, plot this measure on your line chart, and it will always show the last 4 weeks relative to the latest week in the slicer, regardless of the date range selected.

SellThrough_Last4Weeks =
VAR MaxWeekDate =
    CALCULATE (
        MAX ( 'Date'[Date] ),
        ALL ( 'Date' )
    )
VAR Last4Weeks =
    DATESINPERIOD ( 'Date'[Date], MaxWeekDate, -28, DAY )
VAR SalesAmt =
    CALCULATE ( SUM ( Sales[SalesQty] ), Last4Weeks )
VAR StockAmt =
    CALCULATE ( SUM ( Stock[StockQty] ), Last4Weeks )
RETURN
DIVIDE ( SalesAmt, StockAmt )

Hi ash_best_,
Try using ALL or REMOVEFILTERS on your date table in your DAX measure, and calculate sellthrough for the max week selected and 4 previous weeks using DATESINPERIOD. This will ignore the slicer and always show the last 4 weeks.

and try this code:

Sellthrough Last 4 Weeks :=
VAR MaxWeek =
MAX('Date'[WeekNum])
VAR Last4Weeks =
DATESINPERIOD(
'Date'[Date],
CALCULATE(MAX('Date'[Date]), ALL('Date')),
-4,
WEEK
)
VAR TotalSales =
CALCULATE(
SUM('Sales'[SalesAmount]),
Last4Weeks,
REMOVEFILTERS('Date')
)
VAR TotalStock =
CALCULATE(
SUM('Stock'[StockAmount]),
Last4Weeks,
REMOVEFILTERS('Date')
)
RETURN
DIVIDE(TotalSales, TotalStock)

Hi ash_best_ 

Just checking in as we haven't received a response to our previous message. Were you able to review the information above? Let us know if you have any additional questions.

Thank You.

Hi ash_best_ 

I wanted to check if you’ve had a chance to review the information provided. If you have any further questions, please let us know. Has your issue been resolved? If not, please share more details so we can assist you further.

Thank You.