Forum Discussion

gtamir's avatar
gtamir
Icon for Post Patron rankPost Patron
7 years ago
Solved

Date table in multi fact environment

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" )
)

Need Help
  • MFelix's avatar
    MFelix
    7 years ago

    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

4 Replies

  • MFelix's avatar
    MFelix
    Icon for Super User rankSuper User
    7 years ago

    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's avatar
      gtamir
      Icon for Post Patron rankPost Patron
      7 years ago

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

      • MFelix's avatar
        MFelix
        Icon for Super User rankSuper User
        7 years ago

        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