Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to 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
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
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
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 | |||||||||||||||||||||||||||||||||||||||||||||||||
| |||||||||||||||||||||||||||||||||||||||||||||||||
| |||||||||||||||||||||||||||||||||||||||||||||||||
Thanks in advance! |
Thanks,
Neha
User | Count |
---|---|
125 | |
106 | |
99 | |
63 | |
62 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |