Relative Date in Query Parameter

[ivorhooper]

Hi All,

im not sure of the correct terms to describe what i am doing, but here goes...

In Power BI desktop, I am connecting to a SAP Business Warehouse query as my input data. (SapBusinessWarehouse.Cubes).

When I'm adding items into the query, I enter a "key date" as a parameter. How do i make it so that this date is relative to today?

basically, i want this date to be "today -1", ie yesterday's date.

I have a schedule refresh on this query, so it updates every day. However, this is no good to me as every day i have to go into the query and change the "key date" to yesterday's date.

In the advanced editor in the query, the line looks like this:

{Cube.ApplyParameter, "[!V000001]", {"20191001"}}

thanks

Ivor.

[Mariusz · Accepted solution]

Hi @ivorhooper 

You can create a blank query with the code below, the name of the query "YesterdayAsString".

let
    yesterday = 
        Date.AddDays( 
            DateTime.Date( 
                DateTime.LocalNow() ), -1 
        ),
    year = Date.Year( yesterday ) * 10000,
    month = Date.Month( yesterday ) * 100,
    day = Date.Day( yesterday ),
    textDate = Text.From( year + month + day ) 
in
    textDate

Next, adjust your m Expression to 

= {Cube.ApplyParameter, "[!V000001]", { YesterdayAsString }}

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.

[BLB]

Genious, thanks for sharing!

[ivorhooper]

Thanks @Mariusz , this works very well!

[Anonymous]

Hi,

I am trying to do a similar thing, but using SAP HANA as a data source. I tried a few diferent syntax, but it does not work, any ideas?

The code with the value hardcoded looks like below

'PLACEHOLDER' = ('$$keydate$$',#(lf) #(tab) '20180903'