The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event! Join us in Stockholm, Sweden from September 24-27, 2024.
2-for-1 sale on June 20 only!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
Hi,
I would like to create a Dates column or table which I can use as the shared axis for line and bar chart using two different sets dates columns for the line and bar values. I have tried using the formulas on the forum such as the below:
Date =
ADDCOLUMNS (
CALENDAR (DATE(2000,1,1), DATE(2025,12,31)),
"DateAsInteger", FORMAT ( [Date], "YYYYMMDD" ),
"Year", YEAR ( [Date] ),
"Monthnumber", FORMAT ( [Date], "MM" ),
"YearMonthnumber", FORMAT ( [Date], "YYYY/MM" ),
"YearMonthShort", FORMAT ( [Date], "YYYY/mmm" ),
"MonthNameShort", FORMAT ( [Date], "mmm" ),
"MonthNameLong", FORMAT ( [Date], "mmmm" ),
"DayOfWeekNumber", WEEKDAY ( [Date] ),
"DayOfWeek", FORMAT ( [Date], "dddd" ),
"DayOfWeekShort", FORMAT ( [Date], "ddd" ),
"Quarter", "Q" & FORMAT ( [Date], "Q" ),
"YearQuarter", FORMAT ( [Date], "YYYY" ) & "/Q" & FORMAT ( [Date], "Q" )
)
And I keep getting errors. One of them being An error occurred in the ‘’ query. Expression.Error: The name 'Date' wasn't recognized. Make sure it's spelled correctly.
Does anyone know how I can overcome this problem and have a standard dates column I can use for an axis?
Thanks,
Hannah
Hi @HCS,
You should create a data table in Power BI Desktop instead of Edit Query. The formula above is the dax expression which should be used in Power BI Desktop. By my test, the formula is correct.
You could follow the steps in the picture below to create the Date table. Then you could use the date as the shared axis for line and bar chart.
In addition, if you need a simple date table, you could create the data table with the formula below.
CALENDAR (DATE(2000,1,1), DATE(2025,12,31)),
You also could refer to this article to learn how to create a date table with CALENDAR.
Hope it can help you!
Best Regards,
Cherry
Hi Cherry,
Thanks very much for your help.
The dates table has been created which is great. However, as it is a different table from my core data there is no relationship between them and I am having difficulty creating one as there is no common column. Is there a way I can create a simple dates column in my original table and core data?
Thanks,
Hannah
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
99 | |
93 | |
83 | |
64 | |
58 |
User | Count |
---|---|
248 | |
123 | |
112 | |
79 | |
78 |