How to filter data for rolling 24 months using SAP BW BEx Query

[praveenk]

Hello Everyone,

I have to pass two required parameters StartDate and EndDate at the BEx query level.  If I pass Start Date as "20220101" and EndDate as "20220131", these values are fixed in the parameters section. What I'm looking at here is how can I achieve a rolling 24 months of data based on the current date when data flow is refreshed daily. What changes do I have to make in the advanced editor for making this query to bring back rolling 24 months of data from the source system?

For Example: If the data flow is refreshed today (08/23/2022), It has to bring all the data from 08/23/2020 - 08/23/2022.

Any suggestions are much appreciated.

[praveenk]

@amitchandak 

I didn't actually get what you are trying to say. Below is my code, where do you want me to change it?

let

  Source = SapBusinessWarehouse.Cubes("XXX.XXX.", "11", "800", [Implementation = "2.0"]),

  #"Navigation 1" = Source{[Name = "ZCP_BLUE_Q"]}[Data],

  Shipping_Logistics_Blue_Box = #"Navigation 1"{[Id = "ZCP_BLUE_Q/ZCP_BLUEBOX_LOGISTICS_Q001"]}[Data],

  StartDate = Date.AddMonths(DateTime,Date(DateTime.FixedLocalNow()),-24),

  EndDate = DateTime.Date(DateTime,FixedLocalNow()),

  #"Added items" = Cube.Transform(Shipping_Logistics_Blue_Box, {{Cube.ApplyParameter, "[!V000001]", {"20220101", "20220131"}}, {Cube.AddMeasureColumn, "Actual Delivery Qty", "[Measures].[00O2TH886DUSLQ0ERABGKVORJ]"}, {Cube.AddMeasureColumn, "Delivery Quantity", "[Measures].[00O2TH886DUSLQ0ERABGKWE1R]"}, {Cube.AddAndExpandDimensionColumn, "[0COSTELMNT]", {"[0COSTELMNT].[LEVEL01]"}, {"Cost Element Level 01"}}, {Cube.AddAndExpandDimensionColumn, "[HPPTASTRD]", {"[HPPTASTRD].[LEVEL01]"}, {"Transaction Date Level 01"}}})

in

  #"Added items"

[amitchandak]

@praveenk , Use these two dates

Let

  StartDate =Date.AddMonths(DateTime.Date( DateTime.FixedLocalNow()),-24),

  EndDate= DateTime.Date( DateTime.FixedLocalNow()) ,

<Use these two in you code now>

[praveenk]

@amitchandak 

I don't see the data beyond the parameter's value after adding those two lines to the code. Do I have to remove the date values and substitute them with the StartDate and EndDate?

[amitchandak]

@praveenk , Those two date values you try to use these parameters

if needed convert to text

Date.ToText([StartDate],"yyyyMMdd")

replace 20220101 and the other date and check if it works