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...
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.

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.