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. (SapBusinessWar...
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



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



  • ivorhooper's avatar
    ivorhooper
    New Member
    6 years ago

    Thanks Mariusz , this works very well!

    • 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!