Date hierarchy

[J94]

Hello everyone,

I have a graph with year and month in the x axis, which work nicely (see screenshot). This is coming from a data hierarchy, created by Power BI itself when in Time Intelligence auto date/time is enabled. I prefer not to use this, since it creates tables in de background which slows down the performance. 

So, I am looking for a workaround. I do have a monthnyear column as Integer. This isn't working as nice as the date hierarchy, because when you select type as Categorical, it gives me a scrollbar and Continious gives me unequal distances between data points. 

And I can create my own hierarchy with Year and Month, but that gives a scrollbar again. Is there a solution for this which gives the same result as the hierarchy provided when you enable auto date/time? 

[J94]

@quantumudit  thanks, this works exactly as I wished! 

[quantumudit · Accepted solution]

Hello @J94 

The EOMONTH() function can be utilized to retrieve the last date of the month, formatted as "mmm-yy".

This approach ensures that the datatype remains intact, meaning the MonthYear column retains its date/time datatype rather than being converted to categorical.

By applying this method, the desired outcome should be achieved. An image illustrating a scenario is attached. Kindly implement the same technique and inform us if the issue persists.

Best Regards,
Udit

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudo 👍

🚀 Let's Connect: LinkedIn || YouTube || Medium || GitHub
✨ Visit My Linktree: LinkTree

Proud to be a Super User