Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
dEllE
Frequent Visitor

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

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:

dEllE_0-1638961239282.png

 

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.dEllE_1-1638961507341.png

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

 

1 ACCEPTED SOLUTION
v-yalanwu-msft
Community Support
Community Support

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:

vyalanwumsft_0-1639364122397.png

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.

View solution in original post

3 REPLIES 3
v-yalanwu-msft
Community Support
Community Support

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:

vyalanwumsft_0-1639364122397.png

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
Super User
Super User

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

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 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors