Forum Discussion
SELECTEDVALUE to CustomColumn
Hello experts,
need help here,
I have a report with "user selected date graphs and historic(1year old data from user selected date) graphs.
I have a date slicer for this,
> I want to capture user selected date
> create a custom column in the historic table(SQL Import) to calculate the difference in month and pick last 12months of data : DATEDIFF( Hist_date, SELECTEDVALUE(User_selected_date),MONTH) < =12
> Historic graph uses this Hist_Date in the axis to show user_selected minus 12months of data.
But
SELECTEDVALUE(user_slected_date) is always NULL here, if I create a measure with SELECTEDVALUE(User_selected_date) and add that to card on report it shows what User has selected but doesnt work in the DATEDIFF/expression.
Please help how I can solve this.
I was able to figure this out, thanks to other contributors.
basically SELECTEDCALUE cannot be used for creating a Custom Column or even a even creating a table. It can only be used in a Measure.
So I created measures for all my facts and replaced them in the historic graphs of my report and it worked like a charm.
Measure =
var _currdt = SELECTEDVALUE ( User selected date)
var _Prvdt = DATE(YEar (_currdt), MONTH (_currdt) -12, DAY(_currdt))
RETURN
CALCULATE ( Aggregation_col,
hist_date >= _prvdt && hist_date <= _currdt)thanks guys
1 Reply
I was able to figure this out, thanks to other contributors.
basically SELECTEDCALUE cannot be used for creating a Custom Column or even a even creating a table. It can only be used in a Measure.
So I created measures for all my facts and replaced them in the historic graphs of my report and it worked like a charm.
Measure =
var _currdt = SELECTEDVALUE ( User selected date)
var _Prvdt = DATE(YEar (_currdt), MONTH (_currdt) -12, DAY(_currdt))
RETURN
CALCULATE ( Aggregation_col,
hist_date >= _prvdt && hist_date <= _currdt)thanks guys