Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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.

Reply
Powell360
Helper I
Helper I

SET DATEFIRST 1 in Direct Query mode

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?

 

Thanks

7 REPLIES 7
miltenburger
Helper V
Helper V

Hi @Powell360,

 

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.

Kind regards

@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

Hi @Powell360,

You can create an idea here, Power BI will become better after your support.

Best Regards,
Angelia

Angelica,

 

Isn't this a change more practically completed in the Azure SQL package rather than Power BI? 

Hi @Powell360,

Actually, I am not specifc about Azure SQL package, you can posted any question about it in the corresponding forum.

Best Regards,
Angelia

v-huizhn-msft
Employee
Employee

Hi @Powell360,

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,
Angelia

@v-huizhn-msft,

 

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 .

 

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors