Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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
Solved! Go to 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 , 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])
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
@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.
Thanks, AmitChandak,
Very Helpful!
Remy
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
User | Count |
---|---|
86 | |
85 | |
68 | |
67 | |
63 |
User | Count |
---|---|
210 | |
118 | |
116 | |
81 | |
74 |