Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Last 4 weeks sales from specific date

Hi All,   I have been searching for a while and while things are close and I have tried to manipulate the dax, it's not coming out. Creating this column.   I used this link as reference: Need hel...
Need Help
Ashish_Mathur's avatar
Ashish_Mathur
Icon for Super User rankSuper User
2 years ago

Hi,

Assuming the relationships are set up properly, create a Date slicer from the Calendar Table and select 18/2/2024 there.  Now write this measure to get the total sales for the 4 weeks period ended the selected date

Measure = CALCULATE(sum('Sales Accumulation'[Dollar Sales]),Datesbetween('Calendar',min(calendar[date])-27,max(calendar[date])))

Hope this helps.

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi Ashish,

     

    Sorry - I thought I had given a reply and when looking, I did not see it in the post.

    I did try it and it came up with a dax error based.

     

    I was able to use:

    Four week sales = calculate(sum('Sales Accumulation'[Dollar Sales]),DATESINPERIOD('Sales Accumulation'[Week Ending Date],max('Sales Accumulation'[Week Ending Date]),-28,DAY))
     
    Then add:
    Four Week Sales Prior =
        var eightweeksales =calculate(sum('Sales Accumulation'[Dollar Sales]),DATESINPERIOD('Sales Accumulation'[Week Ending Date],max('Sales Accumulation'[Week Ending Date]),-56,day))
        return
        (eightweeksales - [Four week sales])
     
    The numbers are accurate. The issue comes that I have to change the slicer to the corresponding week ending date to make it go back the proper amount of weeks to get to the 2-15-2024 date. In this case with latest data of 3-17-24, I use that slicer. When 9 weeks elapse, I will do the same thing, but I will have to change the date slicer.
     
    When I change the week ending date on slicer, the 4 week calculation seems numbers were slightly off <2%.
    Here is a quick example for some data and then expect result:
    Week EndingSales        
    12/24/2023100        
    12/31/2023150        
    1/7/2024200        
    1/14/2024350        
    1/21/2024100        
    1/28/2024125        
    2/4/2024200        
    2/11/2024250        
    2/18/2024205 Key Date = 2/15/2025     
    2/25/2024180 4 Week Prior = 780      
    3/3/2024150 4 Week Post = 770      
    3/10/2024190        
    3/17/2024250 Then I would do a Change = divide(4 Week Post-4 Week Prior),4 Week Prior,0)
    3/24/2024225        
    3/31/2024280        
    4/7/2024300        

     

    I was looking to make it where the date on comparison would be Week Ending 2-18-2024 as the actual price change date happened on 2-15-2024.

     

    So even though we have data in this set into April, it would still calculate the expected results without having to change the date slicer to 3-17-24.

     

    I appreciate your help.
    MT