cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
payyadi
Frequent Visitor

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. 

1 ACCEPTED SOLUTION
payyadi
Frequent Visitor

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
 

View solution in original post

1 REPLY 1
payyadi
Frequent Visitor

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
 

View solution in original post

Helpful resources

Announcements
User Groups Public Preview

Join us for our User Group Public Preview!

Power BI User Groups are coming! Make sure you’re among the first to know when user groups go live for public preview.

March Update

Check it Out!

Click here to read more about the March 2021 Updates!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors