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
NehaSha
Helper II
Helper II

How to pass date slicer selected value to calculated column?

Hi All, 

 

How to pass date slicer selected value to calculated column?

 

I have tried the below mentioned but nothing is working

= SELECTEDVALUE('Calendar'[Date])

Or

End Dates = CALCULATE(SELECTEDVALUE('Calendar'[Date]),ALLSELECTED('Calendar'[Date]))

 

 

Thanks in advance!

 

Thanks,

Neha

1 ACCEPTED SOLUTION

Hey,

 

I assume you are connecting to your SQL Server database via import mode (as you have mentioned calculated column).

 

If you want to use the slicer selection to control the imported data this is not possible, this can be achieved by using a parameter from inside Power Query. This is not available to the end users if the report is published to the Power BI Service,

 

Regards,

Tom



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

View solution in original post

4 REPLIES 4
TomMartens
Super User
Super User

Hey,,

 

I guess you have to reconsider your solution due to this fact:

 

A slicer selection can not be passed into a calculated column, this is due to the fact that calculated columns are just evaluated during model refresh. This is contrast to a measure that gets evaluated whenever a user u

interacts with the model, like slicer selcection and / or cross highlighting in other visuals.

 

Regards,

Tom



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

Hi @TomMartens 

 

Thanks for your reply! 

 

Is there any possibility to pass the slicer selected date value to sql query date varaible  in edit query. 

 

Help will be appreciated!

 

Thanks,

Neha

 

 

Hey,

 

I assume you are connecting to your SQL Server database via import mode (as you have mentioned calculated column).

 

If you want to use the slicer selection to control the imported data this is not possible, this can be achieved by using a parameter from inside Power Query. This is not available to the end users if the report is published to the Power BI Service,

 

Regards,

Tom



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

Hi @TomMartens  ,

 

Thanks for your valuable reply! 

 

I assume i need to start work on this project from scratch again as you said I am connecting through sql server database by defining the automate variables startdate (first of current month) and enddate  (yesterday date always) and refresh load the data based on date variable 

 

but now user need timeline slicer to choose enddate , now all the calculations depend on the end date variable

selection. 

 

What should i do to achieve this scenario, I tried to load the whole data from table but now confuse how to define This  year last year and difference 

 

 sample SQL table structure loaded in power bi (but in real data loaded  from 2011 till yesterday date for each day for each location)

 

 

startdate

salesIn

salesoff

Wrieoff

Ft

Locationid

 

4/1/2019

10

2

0.5

200

1

 

4/2/2019

8

0

0.7

201

1

 

4/3/2019

7

1

1

202

1

 

4/4/2019

5

0

0.5

203

1

 

4/5/2019

1

2

0.9

204

1

 

4/1/2019

9

4

0.8

205

2

 

4/2/2019

2

3

0.8

206

2

 

4/3/2019

8

0

0.8

207

2

 

4/4/2019

4

1

0.8

208

2

 

4/5/2019

3

1

0.8

209

2

 

4/1/2018

10

2

0.8

210

1

 

4/2/2018

15

5

0.8

211

1

 

4/3/2018

7

1

0.8

212

1

 

4/4/2018

6

0

0.8

213

1

 

4/5/2018

3

1

0.8

214

1

 

4/1/2018

1

2

0.8

215

2

 

4/2/2018

12

7

0.8

216

2

 

4/3/2018

3

2

0.8

217

2

 

4/4/2018

4

0

0.8

218

2

 

4/5/2018

2

2

0.8

219

2

 

Report contain 2 slicer Timelineslicer and LocationID Slicer

      

startdate will always be first of that month 

      

Calculation for FT column,writeoff column value will be always the value based on Enddate

      

calculation for SalesIn,salesoff, writeoff column will be caluculated sum between  startdate and endate 

      

Calculation for PercentageCalculatedColumn =sum salein between startdate and enddate / writeoff basedon timelineslicer end date value

      

EndDate for Timeline Slicer

LocationID Slicer

4/4/2019 (lets assume user selected enddate)

ALL

      

 

      

Matrix Result Expectation

 

 

 

Actual

LY

Difference

 

salesIn

53

58

-5

 

salesoff

11

19

-8

 

Wrieoff

1.3

3.2

1.9

 

Percenatge

40.76923

18.125

22.644231

 

FT

411

431

-20

 

Please help in how to calculate actual , LY and difference column based on timeline slicer selection and how to define data in category actual ,LY,difference

    

Please help how to represent the data in the form of matrix table which will show data on rows as shown in example

    

Thanks in advance!

      

 

Thanks,

Neha

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.