Forum Discussion
Prior Period based on Selected Date Range
- 2 years ago
JS00
Try this solution
Click here to download PBIX from OneDrive
How it works ...
Create relationshipsCreate 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.