Create measure with relative date filtering?

[Anonymous]

Hello All

May I consult with you on how to create 2 Measures with relative date filtering?

1. Last 7-day Sales = Sales amount of 7 days before Invoice Date is Today()

2. Coming 7-day Sales = Sales amount of 7 days after today

Invoice Date	Sales	Last 7-Day Sales (May 11th to 17th)	Coming 7-Day Sales (May 18th to 24th)
5/10/2022	100
5/11/2022	200
5/12/2022	500
5/13/2022	400
5/14/2022	666
5/15/2022	800
5/16/2022	1234
5/17/2022	5000	8800	4495
5/18/2022	400
5/19/2022	500
5/20/2022	650
5/21/2022	890
5/22/2022	920
5/23/2022	1000
5/24/2022	135

Please kindly advise, thanks

Best regards

Fred

[Anonymous · Accepted solution]

Hi @Anonymous ,

Please check the formulas.

Last 7-Day Sales = CALCULATE(SUM('Table'[Sales]),FILTER(ALLSELECTED('Table'),'Table'[Invoice Date]>SELECTEDVALUE('Table'[Invoice Date])-7&&'Table'[Invoice Date]<=SELECTEDVALUE('Table'[Invoice Date])))

Coming 7-Day Sales = CALCULATE(SUM('Table'[Sales]),FILTER(ALLSELECTED('Table'),'Table'[Invoice Date]>SELECTEDVALUE('Table'[Invoice Date])&&'Table'[Invoice Date]<=SELECTEDVALUE('Table'[Invoice Date])+7))

Best Regards,

Jay

[rohit_singh]

Hi @Anonymous ,

You will need to create two measures as shown below:

Last 7-day Sales =

var _currdt = SELECTEDVALUE('7-DayRolling'[Invoice Date])

var _prevdt = _currdt - 7

var _sales =

CALCULATE(

SUM('7-DayRolling'[Sales]),

FILTER(ALLSELECTED('7-DayRolling'),

'7-DayRolling'[Invoice Date]> _prevdt && '7-DayRolling'[Invoice Date] <= _currdt ))

return

if (_currdt = today(), _sales, blank())

Next 7-day Sales =

var _currdt = SELECTEDVALUE('7-DayRolling'[Invoice Date])

var _nextdt = _currdt + 7

var _sales =

CALCULATE(

SUM('7-DayRolling'[Sales]),

FILTER(ALLSELECTED('7-DayRolling'),

'7-DayRolling'[Invoice Date] <= _nextdt && '7-DayRolling'[Invoice Date] > _currdt ))

return

if (_currdt = today(), _sales, blank())

These will give you the desired result

Kind regards,

Rohit

Please mark this answer as the solution if it resolves your issue.
Appreciate your kudos! 😊

[Anonymous]

Thanks Rohit

I have created the 2 measures that you advised.