Select Min and Max Date range slicer value in Power Query not DAX

[NilR]

I have one calendar table joining to other tables. On my calendar table I have live data, and I would like to filter the table based on Slicer selection and create a column =

 List.Max(#"DateTable"[Last Day of Selected Slicer Date]))

is this possible to call in the Min amd Max of Slicer in Power query or M query?

Thanks!

[v-robertq-msft · Accepted solution]

Hey, @NilR

In my opinion, you cannot use the selected Slicer value in the Power query, because the Power query can only transform data from the data source and has no link to the visuals created in the report. The selected Segmentation value can only affect the value of measures created in the report.

What's more, if you want to filter a table to Max(Date) selected in the calendar slicer, you can follow my steps and find out if it's useful:

1. I have created some data based on the date and quantity of Salses from 2018-1-1 to 2020-12-31, like this:

2. I created a Date table to use in Slicer:

Date = CALENDAR(DATE(2018,1,1),DATE(2020,12,31))

3. I created a measure:

Sum of amount =

var _maxdate=MAX('Date'[Date])

return

CALCULATE(SUM(Sales[Amount]),FILTER('Sales',[Date]<=_maxdate ))

4. I created a slicer to place 'Date'[Date] and a table chart and then place it like this:

And I guess this is what you want.

You can download my test pbix file here

If this result is not what you want, you can publish some sample data (without sensitive data) and the expected result.

How to get answers to your question quickly

Best regards

Qin Community Support _Robert Team

If this post helps,then consider Accepting it as the solution to help other members find it faster.

[amitchandak]

@NilR , Min and max of List is possible. But I doubt you can do it using slicer value. Slicer value can be used in measure not in columns

[NilR]

Thank you! How can I filter a table = to Max(Date) selected on Calandar slicer?