Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
I am trying to extract data out of an SQL server using ODBC where i usually need Prior day information, but in case today is monday i would need Friday's information, how can i add this intrecacy in my SQL query so i dont have to pull in huge amount of data to add measures.
Here is what my SQL query looks like right now, im pulling Sysdate-1 and sysdate-3 information, but since the data base has all the historical data it it take significant amount of time.
SELECT SQLServer.Table1.Sequence, SQLServer.Table1.name, SQLServer.Table1.ID, SQLServer.Table2.lastprice, SQLServer.Table3.Currentprice, SQLServer.Table1.statuscode,
FROM (((SQLServer.Table1 LEFT JOIN SQLServer.Table4 ON SQLServer.Table1.Sequence = SQLServer.Table4.Sequence) LEFT JOIN SQLServer.Table2 ON SQLServer.Table1.Sequence = SQLServer.Table2.Sequence) LEFT JOIN SQLServer.Table3 ON SQLServer.Table1.Sequence = SQLServer.Table3.Sequence) LEFT JOIN SQLServer.Table5 ON SQLServer.Table2.spcode = SQLServer.Table5.spcode
WHERE (((SQLServer.Table1.statuscode)='A'and (SQLServer.Table3.Currentprice) = sysdate-1 and (SQLServer.Table3.tpdate) = sysdate-3))
Solved! Go to Solution.
I found what i was looking for SQLServer.Table3.tpdate = case when DAYNAME(CURDATE() - 1) = 'Monday' then CURDATE() - 3 else CURDATE() - 1 end
Thanks for your response. Unfortunately this did not work because it is subracting the date from tpdate instead of filtering the particular date. i also preferably want to do the filtering in the SQL itself or the M query.
I found what i was looking for SQLServer.Table3.tpdate = case when DAYNAME(CURDATE() - 1) = 'Monday' then CURDATE() - 3 else CURDATE() - 1 end
@DarcN8 , refer if this can help
Power BI Workday vs Last Workday- https://youtu.be/MkYLT_GYIbM
If you need a calculation
if(weekday([Date],2) =1, [Date],-3, [Date]-1)
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
User | Count |
---|---|
84 | |
84 | |
65 | |
62 | |
55 |
User | Count |
---|---|
172 | |
108 | |
105 | |
71 | |
70 |