Forum Discussion

noah44's avatar
noah44
Helper I
5 years ago
Solved

Getting Month and Year from a Datetime field with DirectQuery

Hello,

 

I have a report where I want to show hourly, daily, monthly and yearly data.

 

It all worked fine expect for the monthly overview.

 

I tried it like this:

DATEVALUE(MONTH(smrt_eigenverbrauch_wert[Zeit]) & "." & YEAR(smrt_eigenverbrauch_wert[Zeit]))
 
But it always gives me this error message:
Conversion failed when converting date and/or time from character string.
 
I used the same DAX on a report with imported data and there it worked fine.
 
Can someone help me?
Need Help
  • noah44's avatar
    noah44
    5 years ago

    Hi, 

     

    thanks for your answers. Sadly they both did not work.

    But I found a solution that works for me:

     

    DATE(YEAR('Table'[date]), MONTH('Table'[date]), 1)

     

    Best Regards

    Noah

4 Replies

  • Pragati11's avatar
    Pragati11
    Super User
    5 years ago

    Hi noah44 ,

     

    Have you tried something like this:

    Date MMYYYY = FORMAT(ClothingSales[Date],"mmm-yyyy")
     
    This calculation will also give you Month-Year part.
     
    Thanks,
    Pragati
    • noah44's avatar
      noah44
      Helper I
      5 years ago

      Hi Pragati11,

       

      I tried this a while ago but apparently FORMAT can not be used with DirectQuery.

       

      Thanks for your answer

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi  noah44  ,

    I tried your operation and found this error.

    You can convert the "." inside to "/" so that it can be displayed.

    DATEVALUE(MONTH(smrt_eigenverbrauch_wert[Zeit]) & "/" & YEAR(smrt_eigenverbrauch_wert[Zeit]))

     

    Best Regards,

    Liu Yang

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • noah44's avatar
    noah44
    Helper I
    5 years ago

    Hi, 

     

    thanks for your answers. Sadly they both did not work.

    But I found a solution that works for me:

     

    DATE(YEAR('Table'[date]), MONTH('Table'[date]), 1)

     

    Best Regards

    Noah