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.
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)
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 |
---|---|
107 | |
93 | |
77 | |
65 | |
53 |
User | Count |
---|---|
147 | |
106 | |
104 | |
87 | |
61 |