Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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!
Solved! Go to 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.
@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.
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! 🤠
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
Check out the May 2024 Power BI update to learn about new features.
User | Count |
---|---|
89 | |
80 | |
60 | |
60 | |
58 |
User | Count |
---|---|
155 | |
119 | |
104 | |
78 | |
71 |