Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
@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.
thanks of reply
Can you send me link of example
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.
thanks
i have a project in visual studio-tabular model , do i need to write dax instead power query?
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.
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.
@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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
98 | |
97 | |
81 | |
77 | |
66 |
User | Count |
---|---|
126 | |
105 | |
103 | |
81 | |
72 |