Forum Discussion
Creating a new date column by adding the year to a repeating month sequence
Hey thanks,
I just found the solution there https://community.powerbi.com/t5/Desktop/We-can-t-automatically-convert-the-column-to-Date-type/m-p/35127#M12674
It consist on creating a new column with years and concantenate the month and year columns. It was easy for my because my table is constructed with 5 other tables, each one corresponding to 2016/2017/2018/2019/2020. In each table i added a year column. And used the "append Queries" button to get the months sequence and the years corresponding to each.
My table now look like this :
Month Year Date (after formating) January (from table 1) 2016 (from table 1) 01/01/2016 ...... January (from table 2)
2017 (from table 2) 01/01/2017 .........
Here : Date = Table[Month Name] & "1, " & Table[Year]
Meaning that, if i want to refresh my data in january 2021 i just have to add the 2021 table, a new year column (year=2021) and use append again.
Have a good day !
Super UserAnia_Learn , Column are static in nature, you can generate a new column with dateadd . but these value can not be control by some variable
https://docs.microsoft.com/en-us/dax/dateadd-function-dax
Or Using date function
https://www.youtube.com/watch?v=YWo-ZpKM6gU&feature=youtu.be
Hey thanks,
I just found the solution there https://community.powerbi.com/t5/Desktop/We-can-t-automatically-convert-the-column-to-Date-type/m-p/35127#M12674
It consist on creating a new column with years and concantenate the month and year columns. It was easy for my because my table is constructed with 5 other tables, each one corresponding to 2016/2017/2018/2019/2020. In each table i added a year column. And used the "append Queries" button to get the months sequence and the years corresponding to each.
My table now look like this :
| Month | Year | Date (after formating) |
| January (from table 1) | 2016 (from table 1) | 01/01/2016 |
| ...... | ||
January (from table 2) | 2017 (from table 2) | 01/01/2017 |
......... |
Here : Date = Table[Month Name] & "1, " & Table[Year]
Meaning that, if i want to refresh my data in january 2021 i just have to add the 2021 table, a new year column (year=2021) and use append again.
Have a good day !