Forum Discussion
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?
Anonymous 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))RETURNCALCULATE([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???
3 Replies
Anonymous 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))RETURNCALCULATE([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???Thank you so much AllisonKennedy! This worked brilliantly.
You're very welcome. Glad it worked.