Forum Discussion
Adding dates to existing rows in table
hi, Anonymous
If so, try this formula
Table= var _datetable=FILTER(CALENDAR("2017-01-01","2017-12-31"),DAY([Date])=1) return GENERATE(Basic,SELECTCOLUMNS(_datetable,"Mon-year",FORMAT([Date],"MMM-YYYY")))https://docs.microsoft.com/en-us/dax/pre-defined-date-and-time-formats-for-the-format-function
If so, [Mon-year] is a text type, ("Jan-2017" not a date format).
I would suggest you use above formula to create the table and change the format in modeling. In this case, it still the date type.
Best Regards,
Lin
Hi Lin,
So I've created a new table and used your formula. I think I'm almost there but how do I get the syntax of the date to appear "Jan-2017" so "MMM-YYYY"
hi, Anonymous
If so, try this formula
Table= var _datetable=FILTER(CALENDAR("2017-01-01","2017-12-31"),DAY([Date])=1) return
GENERATE(Basic,SELECTCOLUMNS(_datetable,"Mon-year",FORMAT([Date],"MMM-YYYY")))
https://docs.microsoft.com/en-us/dax/pre-defined-date-and-time-formats-for-the-format-function
If so, [Mon-year] is a text type, ("Jan-2017" not a date format).
I would suggest you use above formula to create the table and change the format in modeling. In this case, it still the date type.
Best Regards,
Lin
Worked perfectly, you're a star Lin!
Hello Lin,
maybe you could help me as well. I have a similar problem, but I have a specific year that needs to be add to every row. That's what I have:
Name 2018 2019 2020 A 45 42 768 B 31 13 432 C 45 78 43 D 42 46 65 And that's what I need:
Name Total Year A 45 2018 B 31 2018 C 45 2018 D 42 2018 A 42 2019 B 13 2019 C 78 2019 D 46 2019 A 768 2020 B 432 2020 C 43 2020 D 65 2020 It'd be great if it worked automatically.
Thanks,
Dina
