Forum Discussion

New Member

1 year ago

Solved

DAX last 4 weeks

Hi I want to create a visual, line chart that shows me last 4 weeks of sellthrough (sales/stock). My sales data is captured daily and stock every Monday. I also have a date range slicer. Regardless o...

Need Help

v-karpurapud
1 year ago
Hi ash_best_

1. Create a disconnected Week table:

WeekCalendar = SUMMARIZE( 'Date', 'Date'[WeekStart], 'Date'[YearWeek] )

2. Derive the max week from the slicer selection:

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

3. Sell-through by week measure

Put this measure on the visual Y-axis. It uses the WeekCalendar row context (the axis), pulls sales & Monday stock for that week from the actual tables (ignoring slicer min), and returns BLANK() when the week is not among the last 4 weeks relative to MaxWeek.

SellThrough_ByWeek = VAR _ThisWeek = SELECTEDVALUE(WeekCalendar[YearWeek]) VAR _WeekStart = SELECTEDVALUE(WeekCalendar[WeekStart]) VAR _MaxWeek = [MaxWeek] VAR _Sales = CALCULATE( SUM(Sales[SalesAmount]), FILTER( ALL('Date'), 'Date'[YearWeek] = _ThisWeek ) ) VAR _Stock = CALCULATE( SUM(Stock[StockQty]), FILTER( ALL('Date'), 'Date'[Date] = _WeekStart ) ) VAR _Result = DIVIDE(_Sales, _Stock) RETURN IF( _ThisWeek >= _MaxWeek - 3 && _ThisWeek <= _MaxWeek, _Result, BLANK() )

Set WeekCalendar[WeekStart] as the X-axis (sorted ascending) and use SellThrough_ByWeek on the Y-axis. Keep your Date slicer on Date[Date], as it drives the [MaxWeek] measure. No additional visual-level filters are required, though you may optionally filter out blank SellThrough_ByWeek values for clarity.

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.

v-karpurapud

Community Support

1 year ago

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.

ash_best_

New Member

1 year ago

Hi,

Thank you so much for this solution, this partiatlly works as in I wanted the x axis to display weeks. When i do this it shrinks the range down when I select my date slicer range as less then 4 weeks.

v-karpurapud
Community Support
1 year ago
Hi ash_best_

1. Create a disconnected Week table:

WeekCalendar = SUMMARIZE( 'Date', 'Date'[WeekStart], 'Date'[YearWeek] )

2. Derive the max week from the slicer selection:

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

3. Sell-through by week measure

Put this measure on the visual Y-axis. It uses the WeekCalendar row context (the axis), pulls sales & Monday stock for that week from the actual tables (ignoring slicer min), and returns BLANK() when the week is not among the last 4 weeks relative to MaxWeek.

SellThrough_ByWeek = VAR _ThisWeek = SELECTEDVALUE(WeekCalendar[YearWeek]) VAR _WeekStart = SELECTEDVALUE(WeekCalendar[WeekStart]) VAR _MaxWeek = [MaxWeek] VAR _Sales = CALCULATE( SUM(Sales[SalesAmount]), FILTER( ALL('Date'), 'Date'[YearWeek] = _ThisWeek ) ) VAR _Stock = CALCULATE( SUM(Stock[StockQty]), FILTER( ALL('Date'), 'Date'[Date] = _WeekStart ) ) VAR _Result = DIVIDE(_Sales, _Stock) RETURN IF( _ThisWeek >= _MaxWeek - 3 && _ThisWeek <= _MaxWeek, _Result, BLANK() )

Set WeekCalendar[WeekStart] as the X-axis (sorted ascending) and use SellThrough_ByWeek on the Y-axis. Keep your Date slicer on Date[Date], as it drives the [MaxWeek] measure. No additional visual-level filters are required, though you may optionally filter out blank SellThrough_ByWeek values for clarity.

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.