Relative Date Range in Adobe Analytics

[Anonymous]

So I'm trying to add a relative date range in Power Query Editor. 

I've connected to my Adobe Analytics account and can pull some data through, however i can only hard code a date range. I'm hoping to have a relative date range in the code to pull all data on or after 01 April 18. 

Here's what i have so far with the line of code i need to change:

= Cube.Transform(qantascom,
{
{Cube.ApplyParameter, "DateRange", {#date(2018, 1, 1), #date(2018, 11, 26)}},
{Cube.AddAndExpandDimensionColumn, "DateGranularity", {"year", "month", "day"}, {"Date Granularity.Level 1: Year", "Date Granularity.Level 2: Month", "Date Granularity.Level 3: Day"}},
{Cube.ApplyParameter, "Top", {1000, "page"}},
{Cube.AddAndExpandDimensionColumn, "page", {"page"}, {"Page"}},
{Cube.AddAndExpandDimensionColumn, "evar60", {"evar60"}, {"Page Title"}},
{Cube.AddAndExpandDimensionColumn, "prop7", {"prop7"}, {"Page Title.1"}},
{Cube.AddMeasureColumn, "Unique Visitors", "uniquevisitors"},
{Cube.AddMeasureColumn, "Visits", "visits"},
{Cube.ApplyParameter, "Segment", {{"s200001517_5b74b9fb506c8f23b82f080d"}}}})

Any Ideas?

[Anonymous · Accepted solution]

HI @Anonymous,

I'd like to suggest you modify your formula to parametrized query with query parameters:

Deep Dive into Query Parameters and Power BI Templates

For example: define two parameters with date type and use them to replace your query formula

{Cube.ApplyParameter, "DateRange", {startDate,endDate}},

In addition, you can also consider to use Datetime.Localnow to use local datetime as filter parameter.

Power Query M filter data by Dynamic Date (PQ not DAX) Start of Month

Regards,

Xiaoxin Sheng