Forum Discussion
Need M code for dynamically selecting financial year start and end date picked from a slicer visual
M code cannot dynamically pick values for slicers.
What you can do is add a new column to your table, and using M code have the field be the same values as your slicer values, except for the value you want to be the one always selected - and you call that "Current" - then you pick Current in the slicer and publish.
Today, June 6 is "Current" when it refreshes. Tomorrow it is June 7, and so on.
You can also do this for Current week, month, year, whatever.
For the day, the following code would work:
each if [Date] = DateTime.Date(DateTime.LocalNow()) then "Current" else Text.From([Date])
It returns this. You would have text values in the slicer, but it shouldn't matter. The true date is there and you can still get that via SELECTEDVALUE(SlicerTable[Date]) to work with as a true date.
You could even add a sort by column so Current was always at the top of the slicer.
each if [Slicer Date Value] = "Current" then 1 else Number.From([Date])
The numbers from the date would be those in the 45K range (days from Jan 1, 1900) and current would be 1. So all dates would be in the correct order, just with Current at the top.
edhans Actually, it is the other way around. The slicer selection should pass the values to the m code which in return fetches the data from oracle only for the specific financial year start and end dates.
- edhans3 years agoCommunity Champion
Ok, sorry. I don't spend much time trying to optimize Direct Query to relational databases. Too many downsides for the overwhelming number of my projects. Someone else with more experience in that might want to jump in.