Forum Discussion
Creating a new date column by adding the year to a repeating month sequence
Hello everyone,
I have a table that looks like this ;
| Date | Variable 1 | Variable 2 |
| January | ||
| February | ||
| ...... | ||
| November | ||
| December | ||
| January | ||
| February | ||
| ...... | ||
| November | ||
| December | ||
| ..... (x times) |
|
1- What i want is to create a new column (or transform the existant one) as i will get : January 2016- February 2016 - ..... until today's month for example June 2020.
2- (And if possible i will prefer something that will be refreshed and recognize today's date using the other variables row limit . I mean as if we were working with excel).
Thank you so much
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 !
2 Replies
- amitchandak
Super User
Ania_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 !
