Setting Date Hierarchy - Extract month from the date

[Anonymous]

Hi All,

Every time I load a new set of data into Power Bi, I have to set the Date Hierarchy for the graph:

Graph 1: Initial status

I have to set the Date Hierarchy manually to "month" only so that the graph becomes the shape I need. 

Graph 2: Finished

Is there a way that I can automate this process? Like setting a variable for the month?

Thanks for your ideas.

Regards,

Remy

[amitchandak]

@Anonymous , The better way is to use a date table and have column month there and use that

Month Year = FORMAT([Date],"mmmm")
Month Year sort = month([DAte])

[Anonymous]

Hi Amitchandak,

Thank you for answering my question. 

Can you elaborate a bit more on the answer? It looks great but I cannot fully understand it.

Remy

[amitchandak · Accepted solution]

@Anonymous , Create a date table and join with date of your table

Addcolumns(calendar(date(2020,01,01), date(2021,12,31) ), "Month" , month([date])
, "Year", year([date])
, "Month Year", format([date],"mmm-yyyy")
, "Month year sort", year([date])*100 + month([date])
, "Month",FORMAT([Date],"mmmm")
, "Month sort", month([DAte])

)

Mark month sort as sort column of the month and use month from this table

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.  

[Anonymous]

Thanks, AmitChandak,

Very Helpful!

Remy