Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi Team,
@Anonymous
We have a Bex Query created while importing the data from source we need to fill the filters (start & End Date) . Now the issue is everytime i need to manaually change the filter to see the new data which is tedious , want to avoid manual intervention .
Can anyone please give suggestion for the same.
Regards,
Husna
Solved! Go to Solution.
Hi @Anonymous
You are truely a saver.....
I will be often connecting with you hereafter...
I would like to appreciate you alot . Thank you
Regards,
Husna
Take a look at this post. I posted this a long time ago but have followed this pattern since and haven't had any issues.
https://community.powerbi.com/t5/Desktop/SAP-BW-BEx-Dynamic-Parameters/m-p/806078#M387707
Here is an image from one of my reports. This has been running for a long time but as I look at it there are some things that stick out. I should probably update the SAPStartDate parameter to take the current year - 1 or 2 (Depending on how far back we want to go). I shouldn't have hard coded the start year like that as the users aren't going back to 2017 at this point. This just causes the file to grow with unnecessary data. Also, on my SAPEnddate I have set it to the current year and hard coded the last day of the year. I dont' have as much as an issue with this as this as there is issue with selecting a date in the future like this. That said, each one of these values could be dynamically created based on today's date and what were trying to accomplish. One last thing, I also prefer to create the paramter as the full field value and pass that in as a value (As shown in the pDateFrom & pDateTo). It's been a while since I've had to play with these but I believe by doing it this way you can still interact with your navigation steps where as if you just pass in the parmeter value into the Cube.ApplyParameter Step I beleive that you have issues interacting with the navigation steps. Any way, hopefully this points you in the right direction.
Let me knwo if you have any issues or problems.
lmf232s
Hi @Anonymous
I tried replicating your M code , but i get the below error
For Start and end date i wanted to keep it as previous month, since we get the complete data of the month , i hope i have done the coding correctly for that
Regards,
Husna
Change your code to the following (Removing all the # and ")
CurrentDate = DateTime.LocalNow(),
CurrentMonth = Date.AddMonths(CurrentDate, -1),
Monthex = Number.ToText(Date.Month(CurrentMonth)),
Then pass in this way
[Cube.ApplyParameter, "[ZSUSRPER]", {Monthex, Monthex}},
Keep in mind I hand coded this so there may be a mistake that would prevent you from copying the code but you should get the idea. Notice I removed all the # and " from the fields.
@Anonymous Thank you , Appreciate your assistance in the below
Finally im about to achieve what i needed but need your help here to get the data for previous month , since current month will always be incomplete, i need start date as
2/1/2020 |
and end date as
2/29/2020 |
Though i have done some coding but im not sure if it will give me what i needed, since EndofMonth is throwing error like cant take two parameters.
So there are bunch of different ways to do this. I've given you two examples and tried to break them down to the basics. I'm sure there are some other cleaver ways to pull this off but this is what I have for now.
DateFormatExample
let
CurrentDate = DateTime.LocalNow(),
PreviousPeriod = Date.AddMonths(CurrentDate, -1),
StartOfMonth = Date.StartOfMonth(PreviousPeriod),
EndOfMonth = Date.EndOfMonth(PreviousPeriod),
Year = Date.Year(PreviousPeriod),
Month = Date.Month(PreviousPeriod),
FirstDayOfMonth = Date.Day(StartOfMonth),
LastDayOfMonth = Date.Day(EndOfMonth),
SAPStartDate = Date.ToText(#date(Year, Month, FirstDayOfMonth), "yyyyMMdd"),
SAPEndDate = Date.ToText(#date(Year, Month, LastDayOfMonth), "yyyyMMdd"),
pStartDate = Text.Combine({"[0FISCPER].[K4", SAPStartDate, "]"}),
PEndDate = Text.Combine({"[0FISCPER].[K4", SAPEndDate, "]"}),
DatesCombinedForReadability = Text.Combine({pStartDate, " - ", PEndDate})
in
DatesCombinedForReadability
Will Produce the following
DateFormatExamples w/Function Function
Create a function
(UserDate as datetime, FormatString as text) =>
let
Year = Date.Year(UserDate),
Month = Date.Month(UserDate),
Day = Date.Day(UserDate),
DateToText = DateTime.ToText(UserDate, "yyyyMMdd"),
FormattedDate = Text.Replace(FormatString, "@", DateToText)
in
FormattedDate
Call the function
let
CurrentDate = DateTime.LocalNow(),
PreviousPeriod = Date.AddMonths(CurrentDate, -1),
StartOfMonth = Date.StartOfMonth(PreviousPeriod),
EndOfMonth = Date.EndOfMonth(PreviousPeriod),
pStartDate = fnCreateDateParameter(StartOfMonth, "[0FISCPER].[K4@]"),
pEndDate = fnCreateDateParameter(EndOfMonth,"[0FISCPER].[K4@]"),
DatesCombinedForReadability = Text.Combine({pStartDate, " - ", pEndDate})
in
DatesCombinedForReadability
Output:
See if that helps.
Im really gratefull for your prompt response in your below code i get "K420200201" but i need only "K42020002"how to achieve this?
@Anonymous,
Have you tried playing with the code that I posted above. The code above will do what you need it do to, you simply need to exclude the day part and concatonate the year + month. Because your dealing with fiscaly period you'll have to append a 0 to the front of your month so you get 002 insetaed of 02 but other than that everything you need is posted in the code above.
Give it a shot and if you have a specific issue post the code and we'll see what we can do.
Hi @Anonymous
You are truely a saver.....
I will be often connecting with you hereafter...
I would like to appreciate you alot . Thank you
Regards,
Husna
I appolgize, you can actually keep the #" in all the fields and it works just fine. I guess I've just never done it that way.
I think it may have something to do with the way you are creating the data. Date's in SAP are typically in an integer format '20200101' rather than a true date '01/01/2020', etc.
Can you do me a favor and connect to the query selecting dates from the paramter drop down and then past your M-Code. This will allow you to see the format of the parameters that you need to pass in. For example, if I just connect to the query and select the dates from the drop downs I get the following;
{Cube.ApplyParameter, "[0I_DAYS]", {"[ZSD_IS02___F13].[20200101]", "[ZSD_IS02___F13].[20200201]"},
This allows me to see how we need to format the value that is being passed into the parameter. In my original post we were dealing with Fiscal Period so the values was actually '[0FISCPER].[K1220180001]' and in this case you have to make sure that you are passing in the parameter correctly. You'll have to include the 'K12' part of the value and then append the date to this value.
Does that make sense?
User | Count |
---|---|
126 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |