Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi All,
im not sure of the correct terms to describe what i am doing, but here goes...
In Power BI desktop, I am connecting to a SAP Business Warehouse query as my input data. (SapBusinessWarehouse.Cubes).
When I'm adding items into the query, I enter a "key date" as a parameter. How do i make it so that this date is relative to today?
basically, i want this date to be "today -1", ie yesterday's date.
I have a schedule refresh on this query, so it updates every day. However, this is no good to me as every day i have to go into the query and change the "key date" to yesterday's date.
In the advanced editor in the query, the line looks like this:
{Cube.ApplyParameter, "[!V000001]", {"20191001"}}
thanks
Ivor.
Solved! Go to Solution.
Hi @ivorhooper
You can create a blank query with the code below, the name of the query "YesterdayAsString".
let yesterday = Date.AddDays( DateTime.Date( DateTime.LocalNow() ), -1 ), year = Date.Year( yesterday ) * 10000, month = Date.Month( yesterday ) * 100, day = Date.Day( yesterday ), textDate = Text.From( year + month + day ) in textDate
Next, adjust your m Expression to
= {Cube.ApplyParameter, "[!V000001]", { YesterdayAsString }}
Hi @ivorhooper
You can create a blank query with the code below, the name of the query "YesterdayAsString".
let yesterday = Date.AddDays( DateTime.Date( DateTime.LocalNow() ), -1 ), year = Date.Year( yesterday ) * 10000, month = Date.Month( yesterday ) * 100, day = Date.Day( yesterday ), textDate = Text.From( year + month + day ) in textDate
Next, adjust your m Expression to
= {Cube.ApplyParameter, "[!V000001]", { YesterdayAsString }}
Genious, thanks for sharing!
Hi,
I am trying to do a similar thing, but using SAP HANA as a data source. I tried a few diferent syntax, but it does not work, any ideas?
The code with the value hardcoded looks like below
'PLACEHOLDER' = ('$$keydate$$',#(lf) #(tab) '20180903'
User | Count |
---|---|
101 | |
90 | |
80 | |
71 | |
70 |
User | Count |
---|---|
114 | |
98 | |
97 | |
73 | |
72 |