"Power BI DirectQuery: Formatting MM-YYYY for Live Data & Graphs—Need Help!"

[aj354]

• My SQL Server does not have a dedicated column formatted as MM-YYYY.
• The existing datetime column includes MM-YYYY-DATE AND TIME.
• When I try to create a measure for Month-Year, Power BI automatically moves it to the Filters section, preventing it from being used in a graph.

Given these constraints, how can I:

1. Generate the MM-YYYY format dynamically using DirectQuery?
2. Ensure this format works correctly as an X-axis in a graph instead of being treated as a filter?
3. Maintain live data updates without requiring SQL Server modifications?

 

[GraH - Guido]

With Direct query I believe you have the possibility to add a calculated column with the required format. That field can be used on a chart axis.
Though I do not have any experience with SQL server connections, adding calculated columns on SSAS data cubes works when in Direct Query mode.
The other alternative would be to write SQL code with dateformat to alter that datetime field and use that query in the connection string. That depends whether or not you connect to a single table or not. Though one can write different Power Queries with SQL code in them to load multiple tables in the data model. Then you need to build the data model in PBI.