Prior Period based on Selected Date Range

[JS00]

Hello,

I made a date filter table on PowerBi that has a bi-directional relationship with my Calendar table, the Calendar table has a relationship with my fact table. 

The date filter table is a slicer on my report and allows users to choose stats for past 7 days, past 14 days, and past 28 days.

I want to create a measure so when the user chooses the for the past 7 days, it also shows them the stats for the 7 days prior to the past 7 days. (same for other date ranges). For example, if the user choose the past 7 day today, it will show them 1/24-1/30 stats then stats for 1/17-1/23.

Thank you!

 

[speedramps · Accepted solution]

@JS00 
Try this solution
Click here to download PBIX from OneDrive 

How it works ...

Create relationships

Create measure 

Previous start = 
DATEVALUE(
CALCULATE(MIN(Calandar[Date]), ALL(Calandar))
     -  [Previous duration]
)

Previous end = 
DATEVALUE(
CALCULATE(MIN(Calandar[Date]), ALL(Calandar))
 - 1
)

Previous duration = 
DATEDIFF(
    CALCULATE(MIN(Calandar[Date]), ALL(Calandar)),
    CALCULATE(MAX(Calandar[Date]), ALL(Calandar)),
    DAY
    )

Sales this period = 
SUM(Facts[Amount])

Sales previous day = 
var previousstart = [Previous start]
var previousend = [Previous end]
RETURN
CALCULATE(
SUM(Facts[Amount]),
ALL(datefilter),
Facts[Date] >= previousstart && Facts[Date] <= previousend
)

Create report

Thanks for the clear description of the problem with example data. I wish everyone did that!

This solution works and does exactly what you asked.
So please quickly click the [accept as solution] and the thumbs up button to leave kudos. 

Remember we are unpaid volunteers, and you have got free expert help which took a lot of effort.

One question per ticket please. If you need to change or extend your request then please raise a new ticket.

You will get a quicker response and each volunteer solver will get the kudos they deserve. Thank you ! 

If you quote @speedramps in your next tickets then I will then receive an automatic notification, and will be delighted to help you again.

Please now click the [accept as solution] and the thumbs up button.  Thank you.

[Daniel29195]

@JS00 

this video will help you . https://www.youtube.com/watch?v=d8Rm7dwM6gc&t=792s

f my answer helped sort things out for you, i would appreciate a thumbs up 👍 and mark it as the solution !✅
It makes a difference and might help someone else too. Thanks for spreading the good vibes! 🤠