cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
dyabes
Helper I
Helper I

DATEADD: Dynamic value for the interval parameter

I am trying to compare the current value of a measure against the previous period using DATEADD. 

 CALCULATE(
     [Sessions],
     DATEADD( Dates[Date], -30 , DAY )
)

 

I do not want the interval to be fixed at -30 rather dynamic based on the selected value of the Date filter.

var _firstdate = FIRSTDATE( Dates[Date] )
var _lastdate = LASTDATE( Dates[Date] )
var _datediff = DATEDIFF(   _lastdate, _firstdate, DAY )
return
CALCULATE(
     [Sessions],
     DATEADD( Dates[Date], _datediff , DAY ) )

 

Unfortunately the above DAX does not work. The _datediff returns the correct value but when applied within the CALCULATE it appears to be returning a value zero. Can someone explain why this happens and how to resolve it?

 

1 ACCEPTED SOLUTION
AllisonKennedy
Super User
Super User

@dyabes You need to calculate the interval for ALLSELECTED dates, otherwise it will use the context of the visual you're calculating within. 

 

This measure works: 

 

Previous Period Sales =
VAR Int = CALCULATE(DATEDIFF(MAX(Dates[Date]), MIN(Dates[Date]), DAY), ALLSELECTED(Dates))
RETURN
CALCULATE([Total Sales], DATEADD(Dates[Date], Int, DAY))
 
Though be careful to name the visuals, etc well as it's confusing what it's showing when random periods are selected, might be best used with a relative date slicer only???

Please @mention me in your reply if you want a response.

Check out my Tokyo 2020 report with live stats - KUDOS much appreciated

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

View solution in original post

3 REPLIES 3
AllisonKennedy
Super User
Super User

@dyabes You need to calculate the interval for ALLSELECTED dates, otherwise it will use the context of the visual you're calculating within. 

 

This measure works: 

 

Previous Period Sales =
VAR Int = CALCULATE(DATEDIFF(MAX(Dates[Date]), MIN(Dates[Date]), DAY), ALLSELECTED(Dates))
RETURN
CALCULATE([Total Sales], DATEADD(Dates[Date], Int, DAY))
 
Though be careful to name the visuals, etc well as it's confusing what it's showing when random periods are selected, might be best used with a relative date slicer only???

Please @mention me in your reply if you want a response.

Check out my Tokyo 2020 report with live stats - KUDOS much appreciated

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

View solution in original post

Thank you so much @AllisonKennedy! This worked brilliantly. 

You're very welcome. Glad it worked.


Please @mention me in your reply if you want a response.

Check out my Tokyo 2020 report with live stats - KUDOS much appreciated

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.