create Date table on the basis of several tables

[h_l]

Hi there,

In our data, there is a Date table created on the basis of 1 loaded data:

Date = CALENDAR(MIN('Download'[RequestDate]),MAX('Download'[RequestDate]))

Now, there are more tables got loaded, every table contains a column with date, e.g.

'Sales'[PaymentDate], 'Sales'[OrderDate], 

'Subscribe'[SubDate],

'Shipment'[ShippingDate]

'Active'[ActiveDate]

PS: there is a date column created by using DAX.

Just wanted to ask:

if there is a way to create Date table, the date shall be from the earliest to the latest date in all columns in all table?

e.g.

Earlist date is RequestDate in Download table: 2023-1-1

Latest date is ActiveDate in Active table: 2023-6-7

all other date values in any table are in between above date.

Your advice is apprecaited.

H

[Shalabh_Kushwah · Accepted solution]

yes, this could be possible simply by following these steps.

1. Append all the date columns from all Tables which contains date.

2. Remove the Duplicate dates.

3. This will contain dates which are in all the tables. Lets suppose this table is named as DimDates.

To get all the dates without skipping any date in between. You can create a table.

DimCalendar = calendar(Min(DimDates[Date]),Max(DimDates[Date]))

Thanks and Regards 

Shalabh Kushwaha

[Shalabh_Kushwah]

Yes, Sure

The Way through which we can append the tables.

Step 1: Go to the "Transform Data".

Step 2: Click on "Append Query" in Power Query Editor.

Step 3: Select Tables which you want to Append.

Thanks and Regards

Shalabh Kushwaha

[h_l]

Hi Shalabh_kushwah,

Thanks for your reply. Yes I understand this is a way to append table, but here my question is, the Date is in different column names in different table, but not all in "Date" Column.

So, after append, there will be more than 1 columns, e.g. 'Sales'[PaymentDate], 'Sales'[OrderDate],  'Subscribe'[SubDate], 'Shipment'[ShippingDate], 'Active'[ActiveDate]

On the other hand, I just noticed there is a column created by using DAX, not in the raw data, so the Append function seems cannot process that column.

I was wondering if there is a way to combine all the Date into 1 column.

Please advise.

Thanks.

[Shalabh_Kushwah · Accepted solution]

You can add a custom column with as "Date" in each table which is just a Column from 'Sales'[PaymentDate], 'Sales'[OrderDate],  'Subscribe'[SubDate], 'Shipment'[ShippingDate], 'Active'[ActiveDate] and then go for Appending and remove all other than Date column from Appended Table.

Thanks and Regards

Shalabh Kushwaha

[h_l]

Thanks again Shalabh_Kushwah for the workaround.

[ryan_mayu]

@h_l 

have you tried to use CALENDARAUTO()

CALENDARAUTO function (DAX) - DAX | Microsoft Learn

[h_l]

Thanks for reply. Seems this will return a full year calendar? but the actual data earliest date is not 1st of January, it can be any day, e.g. 3-April-2023. (up to what data will be loaded)

[ryan_mayu]

I don't know if we can have better solution for this. If you want the exact earliest and latest date.

maybe you can try to use below formular to get the earliest and latest date. and put them in the calendar function.

min(min(min(date1,date2),date3),date4)

[h_l]

thanks ryan_mayu for the plan B

[ryan_mayu]

you are welcome

[Shalabh_Kushwah · Accepted solution]

yes, this could be possible simply by following these steps.

1. Append all the date columns from all Tables which contains date.

2. Remove the Duplicate dates.

3. This will contain dates which are in all the tables. Lets suppose this table is named as DimDates.

To get all the dates without skipping any date in between. You can create a table.

DimCalendar = calendar(Min(DimDates[Date]),Max(DimDates[Date]))

Thanks and Regards 

Shalabh Kushwaha

[h_l]

Thanks for reply, would you please explain a little more about how to appened all date columns in different table into 1 created table?