Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors