Forum Discussion

inonb's avatar
inonb
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 DATEPART(DW,@EndApproveddate)=4 THEN @EndApproveddate+3                         WHEN DATEPART(DW,@EndApproveddate)=5 THEN @EndApproveddate+4                         ELSE @EndApproveddate+2                 END

 thanks

Need Help

7 Replies

  • JirkaZ's avatar
    JirkaZ
    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's avatar
      inonb
      Frequent Visitor
      6 years ago

      thanks of reply

      Can you send me link of example

       

       

      • JirkaZ's avatar
        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