I am completely out of ideas.
My data is all structured to look at Monday as the first day of the week but I am using an Azure SQL backend so I can't change the default value which is set to Sunday. The outcome is that all of my data is a week out by the time it gets to power BI because the system user I created uses the default value.
I have tried:
SET Datefirst - "Incorrect syntax near the keyword 'SET'"
EXEC sp_...... "Incorrect syntax near the keyword 'EXEC'"
OpenQuery - Not supported
Change the default value of datefirst on the server level - not supported
Change the default value of datefirst on the db level - not supported
Change the default value of datefirst on the user level - not supported
My unerstanding is that Power BI wraps up my quesry as follows SELECT * FROM ([My Query]). I'd be fired if I tried anything that crude, its the kind of thing you do when you're starting out, is this really the best they have?
Is Power BI definitely ready yet? It seems to be missing SO MANY basic features you'd expect with a reporting suite.
I'm not going to buy a full SQL server just to alter the datefirst variable and I think its silly to suggest I'd have to.
Can anyone help out or point me in the right direction?
I was sturggling with this problem also to set datefirst 1 in my DirectQuery.
What i had to do is get the data from last week (monday till sunday), what i created was following:
SELECT * FROM mytable WHERE CreatedDateTime >= DATEADD(day, -(CASE WHEN DATEPART(DW, GETDATE()) = 1 THEN 7 ELSE DATEPART(DW, GETDATE()) - 1 END + 6), CONVERT(DATE, GETDATE())) AND CreatedDateTime < DATEADD(day, 1 - CASE WHEN DATEPART(DW, GETDATE()) = 1 THEN 7 ELSE DATEPART(DW, GETDATE()) - 1 END, CONVERT(DATE, GETDATE())) AND Minutes is not null AND Minutes > 0 AND TicketNumber like 'CH%'
Hope it helps you to get to set the datefirst to 1, it's all based on my where clause.
@miltenburger thanks for the reply,
It feels to me like they have severly regressed the softwware, I can't understand why such a tiny and simple change should be made so complicated
Monday is the first day of week in Azure SQL, you want to change date format Sunday as the first day of this week when you get date in Power BI desktop in direct Query Mode, right? If it is, we can not change it, you connect date of database directly rather than importing data to the local when you use Direct Query model, please review this feature request below and vote it.
Improve Direct Query Date Time Handling
Thanks for the reply.
I can;t get Azure to see Monday as the first day no matter where I go. I think a few too many features have been turned off unfotunately .
Check out the November 2023 Power BI update to learn about new features.
130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.