Forum Discussion
How to create a parameter with dynamic Date query list
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
Ok, if you need to filter the data for performance reasons and you don't need the historical data for any other visuals, it's easy enough.
See attached (amended) PBIX file.
Here's the code if you prefer...
Create a new blank query and paste this code in the advanced editor...
// StartOfCurrentMonth
let
Source = Date.StartOfMonth(
DateTime.Date(
DateTime.FixedLocalNow()
)
)
in
Source
Main code (look at the last step that does the filtering)...
// data
let
Source = {
Number.From(
#date(
2021,
1,
1
)
)..Number.From(
#date(
2023,
12,
31
)
)
},
#"Converted to Table" = Table.FromList(
Source,
Splitter.SplitByNothing(),
null,
null,
ExtraValues.Error
),
#"Changed Type" = Table.TransformColumnTypes(
#"Converted to Table",
{
{
"Column1",
type date
}
}
),
#"Renamed Columns" = Table.RenameColumns(
#"Changed Type",
{
{
"Column1",
"Date"
}
}
),
#"Added Custom" = Table.AddColumn(
#"Renamed Columns",
"value",
each
Number.RandomBetween(
1257,
15663
)
),
#"Inserted Start of Month" = Table.AddColumn(
#"Added Custom",
"Start of Month",
each
Date.StartOfMonth(
[
Date
]
),
type date
),
#"Grouped Rows" = Table.Group(
#"Inserted Start of Month",
{
"Start of Month"
},
{
{
"value",
each
List.Sum(
[
value
]
),
type nullable number
}
}
),
#"Changed Type2" = Table.TransformColumnTypes(
#"Grouped Rows",
{
{
"value",
Currency.Type
}
}
),
#"Filtered Rows" = Table.SelectRows(
#"Changed Type2",
each
[
Start of Month
] >= StartOfCurrentMonth
)
in
#"Filtered Rows"
I am trying to paste the code to the advanced editor, but can't figure out where did I go wrong:
let
Source = CommonDataService.Database("XXX.com"),
dbo_lvs_opportunityforecast = Source{[Schema="dbo",Item="lvs_opportunityforecast"]}[Data]
dbo_lvs_opportunityforecast = Date.StartOfMonth(
DateTime.Date(
DateTime.FixedLocalNow()
)
)
in
dbo_lvs_opportunityforecast
Bingo!! the query is working!! Thank you so much for sharing and being patience with me 😃
Did you do this step first?
If you have, you should be able to do something like...
(replace placeholder with your date column name)
let Source = CommonDataService.Database( "XXX.com" ), dbo_lvs_opportunityforecast = Source{ [ Schema = "dbo", Item = "lvs_opportunityforecast" ] }[ Data ], #"Filtered Rows" = Table.SelectRows( dbo_lvs_opportunityforecast, each [ ReplaceThisWithTheNameOfYourDateColumn ] >= StartOfCurrentMonth ) in #"Filtered Rows"I renamed Query1 and unchecked Enable Load, it shows a new error:
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