Forum Discussion
Generate a data table based on other table
hi,
i have a table A with a date field 'Orderdate' in it. And i want to create another date table B with only dates between Min('Table A'[Orderdate]) to Max('table A'[Orderdate]).
like this
i have Table A
Orderdate Order Num
10/02/2017 615273
12/24/2017 898003
01/03/2018 466578
05/12/2018 579808
i want to dynamically genertae Date Table B with all dates from 10/02/2017 to 05/12/2018
date
10/02/2017
10/03/2017
10/04/2017
.
.........
till
05/12/2018'
Anonymous ,
According to your description, my understanding is that you want to create a calendar table with continuous data between Min('Table A'[Orderdate]) to Max('table A'[Orderdate]).In this scenario, we can create a calculated table using the function CALENDAR, this function returns a table with a single column named “Date” that contains a contiguous set of dates, please refer to the following query:
Calender = CALENDAR(MIN('table A'[Orderdate]),MAX('table A'[Orderdate]))Calculated tables will be re-calculated if any of the tables it pulls data from are refreshed or updated in any way.
Regards,
Lydia
2 Replies
Anonymous ,
According to your description, my understanding is that you want to create a calendar table with continuous data between Min('Table A'[Orderdate]) to Max('table A'[Orderdate]).In this scenario, we can create a calculated table using the function CALENDAR, this function returns a table with a single column named “Date” that contains a contiguous set of dates, please refer to the following query:
Calender = CALENDAR(MIN('table A'[Orderdate]),MAX('table A'[Orderdate]))Calculated tables will be re-calculated if any of the tables it pulls data from are refreshed or updated in any way.
Regards,
Lydia