Forum Discussion

IF's avatar
IF
Icon for Post Prodigy rankPost Prodigy
2 years ago

Automatic date input

Hi,

I get the data from SAP with the following format:

let
Source = SapBusinessWarehouse.Cubes(................),
#"Added Items" = ..........
{ {Cube.ApplyParameter, "[!V000003]", {#date(2023, 9, 20), #date(2023, 9, 21)}},

 

First date should be yesterday's date and the second date should be todays date. How can i write m code for having this as an automatic input?

As an example, this works for this month and last month:

YMONTHE = Text.PadStart(Number.ToText(Date.Month(DateTime.Date(DateTime.LocalNow()))),2,"0") &"." & Number.ToText(Date.Year(DateTime.Date(DateTime.LocalNow()))-1),

YMONTHS = Text.PadStart(Number.ToText(Date.Month(DateTime.Date(DateTime.LocalNow()))),2,"0") & "." & Number.ToText(Date.Year(DateTime.Date(DateTime.LocalNow()))),

#"Added Items" = Cube.Transform(.........{
{Cube.ApplyParameter, "[0I_CTH]", {YMONTHE, YMONTHS}},

 

thanks in advance

Need Help

5 Replies

  • lbendlin's avatar
    lbendlin
    Icon for Super User rankSuper User
    2 years ago

    What is the expected date format in SAP?  try this:

     

    {Date.From(DateTime.LocalNow()), Date.From(DateTime.LocalNow()+#duration(1,0,0,0))}

    • IF's avatar
      IF
      Icon for Post Prodigy rankPost Prodigy
      2 years ago

      Hi,

      The format that i see when i put the values is: { {Cube.ApplyParameter, "[!V000003]", {#date(2023, 9, 20), #date(2023, 9, 21)}},

       

      in this format. i don't know it helps. I could not try the query yet.

       

      Thanks a lot

    • IF's avatar
      IF
      Icon for Post Prodigy rankPost Prodigy
      2 years ago

      there are two different formats that i am struggling with:

      1. { {Cube.ApplyParameter, "[!V000003]", {#date(2023, 9, 20), #date(2023, 9, 21)}},

        in this case i should be enter the parameter as "2023,9,20"

       

      2.  {Cube.ApplyParameter, "[0I_DN]", {"[GLP].[20231002]", "[GLP].[20231002]"}},

       in this case i should enter the value as "20231002"

       

      I tried this:

      Today = DateTime.Date(DateTime.LocalNow()),
      Yesterday = Date.AddDays(Today, -1),

      {Cube.ApplyParameter, "[0I_DAYIN]", {"[Y4FGLTRP].Yesterday", "[Y4FGLTRP].Today"}},

       

      but it didn't work. i need to provide the input in the same way as i provided in the example.

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi IF ,

    Please try:

    let
    Source = SapBusinessWarehouse.Cubes(................),
    Today = DateTime.Date(DateTime.LocalNow()),
    Yesterday = Date.AddDays(Today, -1),
    #"Added Items" = ..........
    { {Cube.ApplyParameter, "[!V000003]", {Yesterday, Today}}},
    ...
in
    ...

    Best Regards,
    Gao
    Community Support Team

     

    If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
    If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

    How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

    • IF's avatar
      IF
      Icon for Post Prodigy rankPost Prodigy
      2 years ago

      Hi Thanks for the help. I will test it with a friend after he is back from holiday next week.