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