Forum Discussion
Calculating Total Sales for Last 4 week?
Hello,
I am trying to calculate total sales for last 4 week including today date, but I am having really hard time to get it. I tried so many different ways did not work. Some of the dax formulas are below.
Last4weekssales test = CALCULATE(SUMX(SalesDetails,SalesDetails[Sales]),FILTER(ALL(SalesDetails),SalesDetails[Week] = MAX(SalesDetails[Week])-1 || SalesDetails[Week] = MAX(SalesDetails[Week])-2))
Last4weekSales = CALCULATE([TotalSales],
FILTER(ALL(Calendar_dim),
Calendar_dim[YearMonthWeek] >= MAX(Calendar_dim[YearMonthWeek])-1
&& Calendar_dim[YearMonthWeek]<= MAX(Calendar_dim[YearMonthWeek])))None of them did not work. I also attached my file. Can anybody please tell me how to calculate total sales for last 4 week or what is wrong with my file?
Thank you so much
Hello,
Maybe you can try this.
You can Calculate the TotalSales of 4 weeks before or 1 month before:
TotalSales_28DaysAgo:=
Calculate([TotalSales];Dateadd(Calendar_dim;-28;DAYS) or
Calculate([TotalSales];Dateadd(Calendar_dim;-1;MONTH)
TotalSales_Last28Days:=[TotalSales]-[TotalSales_28DaysAgo].
Hopefully this works for you.
Hi Anonymous,
Try this
Last4weeksSales = CALCULATE ( [Sum of Sales Measure], DATESINPERIOD ( 'Table'[Date], LASTDATE ( 'Table'[Date] ), -28, DAY ) )This formula returns the sales for the last 28 days based on the date values in a table. If it's just placed in a card, the reference date will be the max visible date (if your date slicer shows March 28 as the latest date, Mar 28 will be the max visible date).
5 Replies
- danextian
Super User
Hi Anonymous,
Try this
Last4weeksSales = CALCULATE ( [Sum of Sales Measure], DATESINPERIOD ( 'Table'[Date], LASTDATE ( 'Table'[Date] ), -28, DAY ) )This formula returns the sales for the last 28 days based on the date values in a table. If it's just placed in a card, the reference date will be the max visible date (if your date slicer shows March 28 as the latest date, Mar 28 will be the max visible date).
FloriankxSolution Sage
Hello,
Maybe you can try this.
You can Calculate the TotalSales of 4 weeks before or 1 month before:
TotalSales_28DaysAgo:=
Calculate([TotalSales];Dateadd(Calendar_dim;-28;DAYS) or
Calculate([TotalSales];Dateadd(Calendar_dim;-1;MONTH)
TotalSales_Last28Days:=[TotalSales]-[TotalSales_28DaysAgo].
Hopefully this works for you.
Hell, Thank you for the help, but I tried what you have said it did not work. When I add 28 days or 1 Month it will calculate more than 28 days. Usually, last 28 days sales will $ 6.52M but my formula showing $11.56M for 28 days and $13.93M for one month measure.
TotalSales_28DaysAgo = CALCULATE([TotalSales],DATEADD(SalesDetails[Date],-28,DAY)) TotalSales_MonthAgo = CALCULATE([TotalSales],DATEADD(SalesDetails[Date],-1,MONTH))
Any Idea why is that?
Again, thank you so much for the help.
- Floriankx
Hello,
the formulas I gave you calculated the cumulative sales 28 days / 1 month ago.
This is why you have to subtract the value from [Total Sales].