dyanmic date parameters

[inonb]

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

[JirkaZ]

@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]

thanks of reply

Can you send me link of example

[v-xuding-msft]

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]

thanks

i have a project in visual studio-tabular model , do i need to write dax instead power query?

[v-xuding-msft]

Hi @inonb ,

Did you implement it? If yes, please mark the helpful answer as a solution. Welcome to share your own solutions. More people will benefit here.

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.

[v-xuding-msft]

Hi @inonb ,

If you connect with live connection, it is not supported to create parameters. If you use the import mode, I think using DAX formulas or using M language in Query Editor to transform data is based on your requirement. However, if we write with DAX, the formulas and the results will be not shown in Query Editor. 

(Note: we only can create parameters in Query Editor.)

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.

[JirkaZ]

@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