Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago

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 Sun...
Need Help
miltenburger's avatar
miltenburger
Helper V
8 years ago

Hi Anonymous,

 

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

Anonymous's avatar
Anonymous
Not applicable
8 years ago

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

  • v-huizhn-msft's avatar
    v-huizhn-msft
    Microsoft Employee
    8 years ago

    Hi Anonymous,

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

    Best Regards,
    Angelia

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      Angelica,

       

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

      • v-huizhn-msft's avatar
        v-huizhn-msft
        Microsoft Employee
        8 years ago

        Hi Anonymous,

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

        Best Regards,
        Angelia