DAX - Creation of a CALENDAR table based on multiple columns

[CR]

Hi everyone !

I try to create a calendar table based on multiple columns to get the first date and the last date.

Table_1 contains the columns DATE_1, DATE_2 and DATE_3.

• DATE_1 gets dates between 01/01/19 and 01/01/21
• DATE_2 gets dates between 03/01/19 and 31/12/23
• DATE_2 gets dates between 01/01/12 and 31/12/22

I find out to get the first date and the last date for one column:

CALENDAR_TABLE = CALENDAR(FIRSTDATE(TABLE_1[DATE_1]);LASTDATE(TABLE_1[DATE_1]))

But I need to refer to the 3 columns, not only the DATE_1 table !

If I understand, I need to:

1. gather all dates available in these 3 tables in a variable
2. identify the earliest and the latest date in the variable
3. use these earliest and laster as first date and last date in the CALENDAR

Someone could help me on that ?

Regards,

CR

[mwegener]

Hi @CR 

look at this.

CALENDARAUTO()

https://docs.microsoft.com/en-us/dax/calendarauto-function-dax

Regards,

Marcus

Dortmund - Germany
If I answered your question, please mark my post as solution, this will also help others.
Please give Kudos for support.

[CR]

Hi @mwegener 

Thanks for your quick answer. I also tried to generate a calendar with CALENDARAUTO function including a variable gathering my 3 columns coming from TABLE_1.

Calendar_auto = 
GENERATE(
    CALENDARAUTO();
    VAR CurrentDay = UNION(
        SELECTCOLUMNS('Table_1';"table 1";'Table_1'[DATE_1]);
        SELECTCOLUMNS('Table_1';"table 2";'Table_1'[DATE_2]);
        SELECTCOLUMNS('Table_1';"table 3";'Table_1'[DATE_3]))
    RETURN
        CurrentDay)

 In some ways, it worked because it contains all the days of the years 2019 / 2020 / 2021 / 2022 / 2023.

But, all dates are duplicated several times (DD/MM/YYY):

01/01/2019

01/01/2019

01/01/2019

01/01/2019

01/01/2019

02/01/2019

02/01/2019

02/01/2019

02/01/2019

Do you see why ?

Regards,

CR

[mwegener · Accepted solution]

Hi @CR ,

why so complicated?

Calendar_auto = CALENDARAUTO()

Or try this...

CALENDAR_TABLE =
VAR DATE_TABLE =
    UNION (
        SELECTCOLUMNS ( Table_1, "Date", Table_1[DATE_1] ),
        SELECTCOLUMNS ( Table_1, "Date", Table_1[DATE_2] ),
        SELECTCOLUMNS ( Table_1, "Date", Table_1[DATE_3] )
    )
RETURN
    CALENDAR ( MINX ( DATE_TABLE, [Date] ), MAXX ( DATE_TABLE, [Date] ) )

Regards,

Marcus

Dortmund - Germany
If I answered your question, please mark my post as solution, this will also help others.
Please give Kudos for support.

[CR]

Thanks Marcus, this is indeed more simple and... really efficient !

CR