Dynamic M query parameter for Start & End Date

[KamilGH]

Hi,

I want to use M parameters and slicers to allow users to select start and end dates to filter the result of the report.

I created two DateTime parameters "StartDate" and "EndDate" and passed them into the advanced editor using DateTime.ToText in two different ways as the following:

Method 01:

let
Source = Sql.Database("**.***.**.***", "******", [Query="SELECT MEMBERSHIP_ID, BEG_DATESTAMP #(lf)FROM MEMBERSHIP_HISTORY#(lf)WHERE BEG_DATESTAMP >= '"& DateTime.ToText(StartDate,"MM-dd-yyyy") &"'#(lf)AND BEG_DATESTAMP <= '"& DateTime.ToText(EndDate,"MM-dd-yyyy") &"'"])
in
Source

Method 02:

let
Source = Sql.Database("**.***.**.***", "******", [Query="SELECT * #(lf)FROM MEMBERSHIP_HISTORY#(lf)WHERE BEG_DATESTAMP >= '"& DateTime.ToText(StartDate,"MM-dd-yyyy") &"'#(lf)AND BEG_DATESTAMP <= '"& DateTime.ToText(EndDate,"MM-dd-yyyy") &"'"])
in
Source

Both methods are updating the query based on the dates I enter in the parameters.

Then, I created two calendar tables for "Start Date" and "End Date" using the following syntax and bound them to the parameters.

Start Date = CALENDAR(DATE(2019,01,01),TODAY())
End Date = CALENDAR(DATE(2019,01,01),TODAY())

The issue I'm having is that when I create slicers using the date tables and select one of the dates, the data in the other visual disappear and I get the error message in the screenshots below

Does anyone know where I made mistakes?

Thanks in advance.

[KamilGH · Accepted solution]

Hi everyone,

an update.

I upgraded the Power Bi to the latest version and the issue is solved.

Thanks,

Kamil

[Anonymous]

Hi @KamilGH ,

Thanks for your feedback and sharing the solution here. Could you please mark your post as Answered since the problem has been resolved? Thank you.

Best Regards

[Thennarasu_R]

Try this  manage paraneters in Querry Editor

Thanks,
Thennarasu R

[amitchandak]

@KamilGH , has StartDate some default value. 

Also, can you click on Copy details on clipboard and share that

[KamilGH]

Hi @amitchandak 

thanks for responding.

the parameters StartDate and EndDate are have "current Value"

Below are the details of the error message I'm getting:

Feedback Type:
Frown (Error)

Timestamp:
2022-11-01T15:35:10.6783826Z

Local Time:
2022-11-01T11:35:10.6783826-04:00

Release:
July 2021

Product Version:
2.95.983.0 (21.07) (x64)

Error Message:
Query (2, 14) The M parameter 'StartDate' not defined in model or M parameter type is not valid.

Workbook Package Info:
1* - en-US, Query Groups: 0, fastCombine: Disabled, runBackgroundAnalysis: True.

Telemetry Enabled:
True

Snapshot Trace Logs:
C:\Users\*****\AppData\Local\Microsoft\Power BI Desktop\FrownSnapShot519e8ee3-ff05-42bf-b065-9c8d1dbf0fd2.zip

Model Default Mode:
Composite

Model Version:
PowerBI_V3

Performance Trace Logs:
C:\Users\******\AppData\Local\Microsoft\Power BI Desktop\PerformanceTraces.zip

Enabled Preview Features:
PBI_JsonTableInference
PBI_NewWebTableInference
PBI_ImportTextByExample
PBI_ExcelTableInference
PBI_dynamicParameters
PBI_rdlNativeVisual

Disabled Preview Features:
PBI_shapeMapVisualEnabled
PBI_SpanishLinguisticsEnabled
PBI_qnaLiveConnect
PBI_azureMapVisual
PBI_dataPointLassoSelect
PBI_compositeModelsOverAS
PBI_enhancedTooltips
PBI_enableWebView2

Disabled DirectQuery Options:
TreatHanaAsRelationalSource

Cloud:
GlobalCloud

DPI Scale:
100%

Supported Services:
Power BI

Formulas:


section Section1;

shared StartDate = #datetime(2022, 9, 1, 0, 0, 0) meta [IsParameterQuery=true, Type="DateTime", IsParameterQueryRequired=true];

shared EndDate = #datetime(2022, 9, 30, 0, 0, 0) meta [IsParameterQuery=true, Type="DateTime", IsParameterQueryRequired=true];

shared #"Simple (ToText)" = let
Source = Sql.Database("*******", "*****", [Query="SELECT MEMBERSHIP_ID, BEG_DATESTAMP #(lf)FROM MEMBERSHIP_HISTORY#(lf)WHERE BEG_DATESTAMP >= '"& DateTime.ToText(StartDate,"MM-dd-yyyy") &"'#(lf)AND BEG_DATESTAMP <= '"& DateTime.ToText(EndDate,"MM-dd-yyyy") &"'"])
in
Source;

shared #"Simple (filterquery)" = let
filterquery="SELECT MEMBERSHIP_ID, BEG_DATESTAMP #(lf)FROM MEMBERSHIP_HISTORY#(lf)WHERE BEG_DATESTAMP >= '"& DateTime.ToText(StartDate,"yyyy-MM-dd") &"'#(lf)AND BEG_DATESTAMP <= '"& DateTime.ToText(EndDate,"yyyy-MM-dd") &"'",
Source = Sql.Database("*****", "*******", [Query=filterquery])
in
Source;

Thanks,

Kamil