data table in query editor

[HCS]

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

[v-piga-msft]

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

[HCS]

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