Forum Discussion

Frequent Visitor

6 years ago

dyanmic date parameters

Hi i have a report of ssrs, this is the query i want to implement it in power bi, i mean the parameter of dates SELECT * FROM TableA WHERE [Date] >=@StartApproveddate AND [Date]<=CASE WHEN ...

Solution Specialist

6 years ago

inonb In PowerQuery use params from start and end date. Then evaluate the end date using Date.DayOfWeek, do the math and then use the adjusted end date to do the filtering of the data.

inonb
Frequent Visitor
6 years ago
thanks of reply
Can you send me link of example
- JirkaZ
  Solution Specialist
  6 years ago
  inonb First of all you have to create the start and end date parameters in query editor (of DATE type).
  Then the query itself would look something like:
  
  let
  EndDateDW = Date.DayOfWeek(EndDate),
  EndDateAdjusted = if (EndDateDW = 4 or EndDateDW = 5) then Date.AddDays(EndDate, EndDateDW - 1) else Date.AddDays(EndDate, 2),
  
  //Now you can use the StartDate and EndDate params in your query
  Source = ""
  in
  Source
- v-xuding-msft
  Community Support
  6 years ago
  Hi inonb ,
  
  I add a blog that you can reference. The entire blog does not meet your requirement exactly. However, you can reference the Parameter part to understand how to create and use it.
  
  https://radacad.com/create-a-date-dimension-in-power-bi-in-4-steps-step-1-calendar-columns
  
  Best Regards,
  
  Xue Ding
  
  If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
  - inonb
    Frequent Visitor
    6 years ago
    thanks
    
    i have a project in visual studio-tabular model , do i need to write dax instead power query?

Related content