Changing Date Range to yesterday in Advanced query editor?

[Anonymous]

I am trying to change the date range for my query to have a dynamic end date (yesterday) where the query will pull in data up until sysdate()-1 (SQL syntax) when it is refreshed. How would i do this in DAX? In the code below I would like to change the #daterange to {#date(2019, 2, 10), #sys.date()-1}

{
            {Cube.ApplyParameter, "DateRange", {#date(2019, 2, 10), #date(2019, 5, 29)},
            {Cube.ApplyParameter, "Segment", {{"s300001442_56f2fd0ce4b0087c7199b66e"}}},
            {Cube.AddAndExpandDimensionColumn, "DateGranularity", {"year", "month", "day"}, {"Date Granularity.Level 1: Year", "Date Granularity.Level 2: Month", "Date Granularity.Level 3: Day"}},
            {Cube.AddAndExpandDimensionColumn, "prop10", {"prop10"}, {"10. Properties"}},
            {Cube.AddMeasureColumn, "Page Views", "pageviews"},
            {Cube.AddMeasureColumn, "Unique Visitors", "uniquevisitors"},
            {Cube.AddMeasureColumn, "Visits", "visits"}
        })

[v-piga-msft · Accepted solution]

Hi @Anonymous ,

If I understand your requirement correctly that you want to get data based on the date range (2019-2-10, today-1) in power bi?

However, I still have a little confused about how do you want to achieve that? DAX or Power Query in Advanced Editor?

For Power quey, we could use Date.AddDays(DateTime.FixedLocalNow(), -1) to get the yesterday date.

For DAX, we could use DATEADD(DateTime[DateKey],-1,day) to calculate the last day data.

In addition, we could use relative date slicer to filter the data for last 1 day.

If you still need help, feel free to ask.

Best  Regards,

Cherry

[mr_a]

Hi Cherry

I would like to know how you would add the above query in the advanced editor. I am looking for exactly the same solution.
Here is my date range in Advanced editor: How would I replace the last part of my query and switch with yours Date.AddDays(DateTime.FixedLocalNow(), -1) t

{Cube.ApplyParameter, "DateRange", {#date(2022, 6, 20), #date(2022, 6, 26)}},     <--I would like to replace this part ,#date(2022,6,26)  to yesterday date.  
So every day the end date would automatically change from 26 june to 27 to 28 june, and so on.
Can you please help me with the above code. 

[mr_a]

actually I found the solution. I replaced the end date  #date(2022,6,26) with this code 

Date.AddDays(DateTime.Date(DateTime.LocalNow()), -1)

[Anonymous]

I was looking for how to do it in Power Query. This worked, thank you!