dynamically set current date to slicer date selection

[kaileena]

Hello, 

I have a slicer with Date column 

And I will show a snap shot of my calendar table from the query editor. I want the value of the "current date" to be what ever value i select from slicer. I want the current date to "read" what ever value i select from date slicer. Here is a snap shot from the query editor. I have copied the calendar table from another source, so i do not own it. 

regards,

kaileena

[v-eqin-msft · Accepted solution]

Hi @kaileena ,

Agree with @edhans that there is no way to pass the selection in slicer back to Power Query though.

You may create a measure to dynamically get the offset:

Offset = DATEDIFF(MAX('Table'[Date]),MAX('Dates'[Date]),MONTH)

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[kaileena]

Hello! thank you. 

[edhans]

Unfortunately you cannot do this. The selected value(s) in a slicer are locked to what you had selected when you published and they cannot dynamically be changed via DAX.

[kaileena]

Hello! I would like my query editor to "read" the value I selected in the slicer. (i need this because i have other columns that calculte the month offset. I want to calculate the month offset with respect to slicer selection)

I hope it is clearer. I will edit the text above.

[edhans]

Power Query cannot see anything in the DAX model nor in the report layer. In DAX, you can use SELECTEDVALUE() or ALLSELECTED() to see what the user has selected. There is no way to pass that back to Power Query though. Power Query to the Model is a one way street - Power Query loads data in.

An exception to that is with a direct query model, but that is only to allow PQ to create SQL code and cannot see the report layer. It just talks to the storage engine.