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!

Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!