Compare sales values last 7days with 7days before "last 7days"

[dEllE]

Hello,

1st post, so I hope I will not mess up 😉

I got the following tables:

• Calender (date table)
• Calender periods (table created out of calender table to create date ranges to choose from)
• Sales table

  The calendar periods I create with...

Calender Periods = 
var _last30days = ADDCOLUMNS(CALCULATETABLE('Calender',  DATESBETWEEN('Calender'[Date], TODAY() - 31, TODAY() -1)), "Range", "last 30 days")
var _last07days = ADDCOLUMNS(CALCULATETABLE('Calender',  DATESBETWEEN('Calender'[Date], TODAY() - 08, TODAY() -1)), "Range", "last 07 days")
var _yesterday = ADDCOLUMNS(CALCULATETABLE('Calender',  DATESBETWEEN('Calender'[Date], TODAY() - 01, TODAY() - 01)), "Range", "yesterday")
var _next07days = ADDCOLUMNS(CALCULATETABLE('Calender',  DATESBETWEEN('Calender'[Date], TODAY() + 1, TODAY() + 08)), "Range", "next 07 days")
var _next30days = ADDCOLUMNS(CALCULATETABLE('Calender',  DATESBETWEEN('Calender'[Date], TODAY() + 1, TODAY() + 31)), "Range", "next 30 days")
return
UNION(_last30days, _last07days, _next07days, _next30days, _yesterday)

 ...and connected like this:

I want to compare sales of last 7 days (or last 30days) with the sales one week before "last 7days".

So I created measure:

Sales amount -7 days = 
CALCULATE(SUM(Sales[Sales amount]),
    DATEADD(Calender[Date], -7, DAY)
)

When I now use the column "Range" from Calender Periods table as a slicer, the dates from calender tables get cut to only last 7 days.

As far so good.

But this is, why my measure "Sales amount -7 days" does not work anymore.

How do I "free" the date filter für the -7 days calculation?

Attached pbix file:

Compare 7 days pbix file 

Thx for your help.

dEllE

[v-yalanwu-msft · Accepted solution]

Hi， @dEllE ；

You could modify the measure such as:

VAT Base Amount w Ship Dateadd -7 days Msr = 
CALCULATE(SUM(sales[Sales amount]), FILTER(ALL('Calender Periods'),DATEDIFF([Date],MAX('Calender'[Date]),DAY)=7))

The final output is shown below:

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@dEllE , Measure

based on selected date

Rolling 7 = CALCULATE(Average(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-7,DAY))

based on today

Rolling 7 = CALCULATE(Average(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],today() ,-7,DAY))

& before that

Rolling 7 b 7= CALCULATE(Average(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]) -8 ,-7,DAY))

based on today

Rolling 7 b 7= CALCULATE(Average(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],today()-8 ,-7,DAY))

[dEllE]

Hi Amit,

Rolling 7 b 7 does not work.

When calculating like this, the amount per day is not correct anymore..

Attached please find the pbix file.

Compare 7days pbix file