Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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'
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
115 | |
100 | |
88 | |
68 | |
61 |
User | Count |
---|---|
150 | |
120 | |
100 | |
87 | |
68 |