Date table in multi fact environment

[gtamir]

Can anyone help me to complete the formula?
I want to build myself the date table in a Multi Fact Table environment but I have an error. Thanks

DATE = ADDCOLUMNS(CALENDAR (DATE(YEAR (MIN
(MIN(FactInternetSales[DueDate]))),1,1),
(MIN(FactResellerSales[DueDate]))),1,1), 
(MAX
(MAX(FactInternetSales[DueDate]))),12,31),
(MAX(FactResellerSales[DueDate]))),12,31),
"Year", YEAR ( [Date] ), 
"Month Number", MONTH ( [Date] ),
"Month Name", FORMAT ( [Date], "mmmm" ),
"Quarter", "Q" & INT ( FORMAT ( [Date], "q" )
)

[MFelix · Accepted solution]

HI @gtamir ,

Asuming that you want the max and minimum year of the two refer columns to get beginning and ending of calendar try the following:

DATE =
ADDCOLUMNS (
    CALENDAR (
        DATE ( YEAR (
            MIN ( MIN ( FactInternetSales[DueDate] ), MIN ( FactResellerSales[DueDate] ) )
        ), 1, 1 ),
        DATE ( YEAR (
            MAX ( MAX ( FactInternetSales[DueDate] ), MAX ( FactResellerSales[DueDate] ) )
        ), 12, 31 )
    ),
    "Year", YEAR ( [Date] ),
    "Month Number", MONTH ( [Date] ),
    "Month Name", FORMAT ( [Date], "mmmm" ),
    "Quarter", "Q" & INT ( FORMAT ( [Date], "q" ) )
)

Regards,

MFelix

[gtamir]

@MFelix Yes it works. Now I'll have to find what was my mistake. Thanks.

[MFelix]

Hi @gtamir ,

Just picking up the first date on your calendar formula:

DATE(YEAR (MIN
(MIN(FactInternetSales[DueDate]))),1,1),
(MIN(FactResellerSales[DueDate]))),1,1)

In this you are picking up the DATE of the Year of the minimum of two dates and then adding them together, however you formula does not says that.

You have a DATE formula that has the following parameters (YEAR, MONTH, DAY) then you add YEAR that the paremeter is a DATE.

When you place the MIN(FactInternetSales[DueDate]))),1,1) you are adding the January first to a date column so the output is incorrect because the DATE part for the YEAR formula already comes from the MIN(FactInternetSales[DueDate]).

What I do in my formula is getting the DATE with MONTH and DAY being hard coded ( 1,1  or 12,31) and then picking up the YEAR of the MIN of the two dates.

Regards,

MFelix

[gtamir]

Thank you.