Forum Discussion

ivorhooper's avatar
ivorhooper
New Member
6 years ago
Solved

Relative Date in Query Parameter

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.

Need Help
  • Mariusz's avatar
    Mariusz
    6 years ago

    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.
    Mariusz Repczynski



4 Replies

  • Mariusz's avatar
    Mariusz
    Community Champion
    6 years ago

    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.
    Mariusz Repczynski



      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        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'

    • BLB's avatar
      BLB
      Helper I
      4 years ago

      Genious, thanks for sharing!