Forum Discussion

sonya7's avatar
sonya7
Helper III
2 years ago
Solved

Creating a dynamic date parameter with m query in Power BI

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 = 

 

 

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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.

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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.