Getting Month and Year from a Datetime field with DirectQuery

[noah44]

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?

[noah44 · Accepted solution]

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

[v-yangliu-msft]

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.

[Pragati11]

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]

Hi @Pragati11,

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

Thanks for your answer