How to Fix this Issue with a Customized Date Table

[Waleed18574]

I wanted to make a date lookup table (DateTable) to connect it with a transactions table (fTransactions). I used the function Calender to initiate the table with the command:

DateTable = CALENDAR(DATE(YEAR(MIN(fTransactions[Date])),1,1), DATE(YEAR(MAX(fTransactions[Date])),12,31))

This initiated the table with one column (Date) that included the dates.

Then I added the following columns:

Year = DateTable[Date].[Year]

MonthNo = DateTable[Date].[MonthNo]

Month = DateTable[Date].[Month]

QuarterNo = DateTable[Date].[QuarterNo]

Quarter = DateTable[Date].[Quarter]

Fiscal year = IF(DateTable[MonthNo]<4, DateTable[Year]-1,DateTable[Year])

Fiscal QuarterNo = IF(DateTable[QuarterNo]=1,4,DateTable[QuarterNo])

Fiscal Quarter = "Q"&DateTable[Fiscal QuarterNo]

Fiscal Period = DateTable[Fiscal Quarter]&"-"&DateTable[Fiscal year]

SortFiscalPeriod = DateTable[Fiscal year]*10+ DateTable[Fiscal QuarterNo]

I sorted the Month column by the MonthNo column and the Fiscal Period column by the SortFiscalPeriod column

Then, I marked it as a date table using the Date. Once I did, I received errors in every column except the first column (Date).

So...how to fix it?

Thanks

[PhilipTreacy · Accepted solution]

@Waleed18574 

Try adding extra columns like this e.g

Year = YEAR(DateTable[Date])
MonthNo = MONTH(DateTable[Date])
Month = FORMAT(DateTable[Date], "mmm")

By using the .[Year] etc notation you are referencing the in built auto Date Time feature in PBI.

When you mark your new table as the Date Table, this auto Date Time is inaccessible so all your column definitions break.

Regards

Phil