Forum Discussion

shakirkhanani's avatar
shakirkhanani
Helper I
8 years ago
Solved

How to convert SQL syntax to Dax

Is it possible to convert following command into Dax, in order to convert Georgian date into Hijri:

With respect to SQL Server 2012:

SQL Serverer 2012 introduces a new Format function that used to achieve the same result.

SELECT FORMAT ( GETDATE(), 'dd/MM/yyyy', 'ar-SA' )

Output

16/08/1435

 

Ref: https://blog.devoworx.net/2014/06/14/how-to-convert-gregorian-dates-to-hijri-date-in-sql-server-2008-2012/#more-761

 

Thanks in advance.

 

Regards,

M Shakir

Need Help
  • shakirkhanani's avatar
    shakirkhanani
    8 years ago

    HI v-xjiin-msft,

     

    Thank you for taking part in my small request.  

     

    I will definitely share under IDEA.

     

    My work around was simillar as suggested by yourself and other participant.  Under excel, in one column I entered dates, convered them in Hijri and format them as Text, and connected this table to my Power BI Desktop, and relate them with the Date table. 




    This help me get the desired results.

11 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    Hi shakirkhanani,

     

    Try  FORMAT(Table[Date],"DD/MM/YYYY")

     

    Hope this works.

      • Anonymous's avatar
        Anonymous
        Not applicable
        8 years ago

        Could you try replace in Edit Query (screenshot attached), and see if it works? 

         

         

         

         

    • precedence's avatar
      precedence
      Frequent Visitor
      8 years ago

      I'm guessing the column isn't set to Data Type: Date

  • v-xjiin-msft's avatar
    v-xjiin-msft
    Solution Sage
    8 years ago

    Hi shakirkhanani,

     

    As I know, currently we are using Georgian date format in Power BI Desktop no matter which region you are in. And it is unable to convert Georgian date into Hijri date within DAX. For the Hijri date format in Power BI, I would suggest you to submit an Idea here for a feature request.

     

    Then in your scenario, if you still want to convert the date I would suggest you to do the work at SQL Server side and import the data into Power BI. But please be noticed that after converting, it is a text type value and is no longer a date type value.

     

    Thanks,
    Xi Jin.

     

     

    • shakirkhanani's avatar
      shakirkhanani
      Helper I
      8 years ago

      HI v-xjiin-msft,

       

      Thank you for taking part in my small request.  

       

      I will definitely share under IDEA.

       

      My work around was simillar as suggested by yourself and other participant.  Under excel, in one column I entered dates, convered them in Hijri and format them as Text, and connected this table to my Power BI Desktop, and relate them with the Date table. 




      This help me get the desired results.

      • v-xjiin-msft's avatar
        v-xjiin-msft
        Solution Sage
        8 years ago

        Hi shakirkhanani,

         

        Thanks for shareing us your solution. Please kindly mark it as answer. It will benefit others with the relevant issue. :smileyhappy:

         

        Thanks,
        Xi Jin.