Forum Discussion
calendar cannot be blank
Hi waleed111 ,
Sorry for the followup questions but to what I uderstand you want to had all the dates in other tables, so if you have a table 1 that has let's assume values for the 1st week for January and you maximum date is the last day of january you table will have a lot of blanks values except for the date:
Table1 (before column)
| Date | Value |
| 01/01/2020 | 10 |
| 02/01/2020 | 30 |
| 03/01/2020 | 40 |
| 04/01/2020 | 50 |
| 05/01/2020 | 60 |
Table1 (after column)
| Date | Value | New Date |
| 01/01/2020 | 10 | 01/01/2020 |
| 02/01/2020 | 30 | 02/01/2020 |
| 03/01/2020 | 40 | 03/01/2020 |
| 04/01/2020 | 50 | 04/01/2020 |
| 05/01/2020 | 60 | 05/01/2020 |
| 06/01/2020 | ||
| 07/01/2020 | ||
| ... | ||
| 29/01/2020 | ||
| 30/01/2020 | ||
| 31/01/2020 |
Is the representation above correct?
If that is the expected result, and once again I apologize why don't you use a calendar table to make the relationship with you table that will return the values that you need and make it more dinamic without the need to duplicate values. Also this type of merge is better made in Power Query than in dax because when you want to had new rows you need to make a new table.
my question is: can i assign the CALENDAR function with a column and if i can what is the conditions?
Hi waleed111 ,
Not really sure what you mean by assign calendar function with a column?
The calendar function returns a table with a set of contiguous dates within the specified range, so basically you define the start and end date and it returns all the dates in between, this can be used to create a table on your model to use with different purposes.
Sorry once again but can you be more specific to tell what is the final result you want to achieve with this table/column on your model.
i want to make unified date from all tables to use it in any report,
my question is can make a column by using CALENDAR function or it work with table just.
Hi waleed111 ,
The calendar function creates a table and cannot be used to create a new column on another table, it will return the an error of multiple values were supplied where a single value is expected.
You can create a calendar table that get's the dates from all the date columns from your model and that is CALENDARAUTO but this has some limitations.
Please check the blog from SQLBI where they explain how to use both functions.
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/