Forum Discussion

RL_171's avatar
RL_171
Frequent Visitor
3 years ago
Solved

How to create a parameter with dynamic Date query list

I want to create a Power BI report for sales forecasting. The report should present opportunities whose Date is equal to current month and future months in 12 month max.  Suppose the oppotunity table...
Need Help
  • KNP's avatar
    KNP
    3 years ago

    We're getting there ğŸ˜„

    You can't compare date and datetime types.

     

    Change the StartOfCurrentMonth query to...

    // StartOfCurrentMonth
let
    Source = Date.StartOfMonth(
        DateTime.FixedLocalNow()
    )
in
    Source

     

RL_171's avatar
RL_171
Frequent Visitor
3 years ago

I created a Query like this:

 

And then copy the following code to the Advanced Editor

 

let
Source = CommonDataService.Database(
"XXX.com"
),
dbo_lvs_opportunityforecast = Source{
[
Schema = "dbo",
Item = "lvs_opportunityforecast"
]
}[
Data
],
#"Filtered Rows" = Table.SelectRows(
dbo_lvs_opportunityforecast,
each
[
lvs_date
] >= StartOfCurrentMonth
)
in
#"Filtered Rows"

 

error message says: Expression.Error: The name 'StartOfCurrentMonth' wasn't recognized

KNP's avatar
KNP
Super User
3 years ago

Rename 'Query1' to 'StartOfCurrentMonth'.

(also, you can right click on this query and untick Enable Load)