Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
praveenk
Frequent Visitor

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

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_0-1661310751303.png

4 REPLIES 4
praveenk
Frequent Visitor

 
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
Super User
Super User

@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>

@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?

 

praveenk_0-1661312170174.png

 

 

 

@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

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.