Creating a dynamic date parameter with m query in Power BI

[sonya7]

Hello everyone, the user must select the start and end date from two separate date tables. I created parameters for these. However, I want to limit my end date to 90 days from the selected start date. Although I think I used the appropriate query for this, I realize that it does not work according to the start date I selected from the slicer on the frontend. How and in what way can I do this?

note: this date slicer seems very difficult, it is very difficult to go down the list, I can take your suggestion for a different way

start date = 

finish date = 

parameters = 

[Anonymous · Accepted solution]

Hi @sonya7 

You can refer to the following solution.

1.I create a paramater and set its type to Text, then input the currentvalue, such as'20240530‘

2.In advanced Editor , change the code to the following.

let

    Source = Sql.Database("xxx", "xxx", [Query="
     select * from xxx where [Date]>= ' " & Parameter1 & "' and [Date]<=DATEADD(DAY, 90, ' " & Parameter1 & "')  "])
in
    Source

Then close and apply it, in power desktop, create the following calendar table.

Calendar = ADDCOLUMNS(CALENDAR(DATE(2024,5,1),TODAY()),"Format",FORMAT([Date],"YYYYMMDD"))

Then click the Format column and bind it to the paramater.

Then put the format column to the slicer, it can work.

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.