Calculating Total Sales for Last 4 week?

[Anonymous]

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

[Floriankx · Accepted solution]

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.

[danextian · Accepted solution]

 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).

[Anonymous]

@danextian and @Floriankx

Hello Guys, 

Both formula works and gave me the same answer. I really appreciate your help.  

Thank you 

[Floriankx · Accepted solution]

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.

[Anonymous]

@Floriankx

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].