Forum Discussion

lona25's avatar
lona25
Regular Visitor
2 years ago

Date Table

 I have four columns related to date, when I tried to mark as date table it shows error as date column must have unique columns and data column can't  have gaps. Column contains identical dates since...
Need Help
Fowmy's avatar
Fowmy
Super User
2 years ago

lona25 

In order for you to mark a table as date table, there are some requirements:


It must have a column of data type date (or date/time)—known as the date column.
The date column must contain unique values.
The date column must not contain BLANKs.
The date column must not have any missing dates.
The date column must span full years. A year isn't necessarily a calendar year (January-December).
The date table must be marked as a date table.


And, you youcan obviously make a fact table as  a date table as it breaks the above conditions. 
The solution and the best approach is to create a separate dates table and create a relationship to your sales table. You can use the following DAX code to create your dates table, make necessary chagnes as needed.

Dates = 
ADDCOLUMNS(
    CALENDAR("01/01/2020","31/12/2021"),
    "Month No" , MONTH([Date]),
    "Month Name", FORMAT([Date],"Mmmm"),
    "Year",YEAR([Date]),
    "Year Month No", INT(FORMAT([Date],"yyyymm")),
    "Year Month" , FORMAT([Date],"Mmm yyyy"),
    "Quarter No", QUARTER([Date]),
    "Quarter" , FORMAT([Date],"\QQ")
)