Forum Discussion

oslosa's avatar
oslosa
Helper I
6 years ago
Solved

Dynamic data retrieval based on today's date

Hi, I am trying to retrieve data for the most recent date from an SAP BW server. It requires me to query a parameter in the following format "[0FISCPER].[K4202003]" to retrieve March 2020 data.   W...
Need Help
  • artemus's avatar
    artemus
    6 years ago

    oh, the solution I gave can easily be fixed... just add a 0 e.g.

    yyyy0MM

ImkeF's avatar
ImkeF
Community Champion
6 years ago

HI just to be specific here:

This is delivering the error-message you've posted below?:

 

{Cube.ApplyParameter, "[!V000003]", {{ Text.Combine({"[0FISCPER].[K4",
Text.From(Number.From(Date.Year(DateTime.LocalNow()))*100+Number.From(Date.Month(DateTime.LocalNow())))
,"]"})}}}

 

 

  • ImkeF's avatar
    ImkeF
    Community Champion
    6 years ago

    Hm, actually a bit at a loss here - how can this formula actually work?

    Wouldn't it have to be:

     

    {Cube.ApplyParameter, "[!V000003]", {{ Text.Combine({"[0FISCPER].[K4" &
    Text.From(Number.From(Date.Year(DateTime.LocalNow()))*100+Number.From(Date.Month(DateTime.LocalNow())))
     
    & "]"})}}}

     

    Please check your exact coding again.

    • oslosa's avatar
      oslosa
      Helper I
      6 years ago

      Of course, the error was that I should have multiplied by 1000! I wanted 2020003 and not 202003

      • artemus's avatar
        artemus
        Microsoft Employee
        6 years ago

        What locale are you using.

         

        If you create a new blank query with:

        = Text.From(<<Your logic>>)

        what is the ouput?

  • oslosa's avatar
    oslosa
    Helper I
    6 years ago

    Yes, that is what I tried to use.

    • artemus's avatar
      artemus
      Microsoft Employee
      6 years ago

      How about trying:

       

      DateTimeZone.ToText(DateTimeZone.FixedUtcNow(), "yyyyMM", "en-us")

       

      Edit: lower case y