I'm trying to create a dynamic table with the next 12 months. I'm sort of halfways with an index column and DATEADD, but it won't cross into 2020.
I realize this is very likely not the best or simplest method of getting what I want.
Ideally I'm looking for a table with a full date key for the start of every next 12 months and then additional columns of month, year. So when we get into February the last row will show january again in 2020.
Has anyone created anything like this?I have a relationship between this (from Month +1 to datekey) and a full Date Dimension table.
What is your desired result? Could you want to add one year for your original date? If so, you could just add 365 for your date:
If I misunderstand you, could you please post me your desired result if possible?
I want a dynamic table that has this current month as the first one and then the next 11 following. So that it updates continually on its own.
I've tried with an index starting at 0.
So first row is
This Month + Index 0
Next row is
This month + Index 1
And so on, so that the first row is always the current month and the next will be next month and so on until you have the next 21 months in total. I just can't seem to get Dateadd to go beyond and into next year.
do you mean something like this? it's calculated table based on TODAY, so if you have some other reference for current month you can just adjust the first variable
Table = VAR __CurrentMonth = DATE(YEAR(TODAY()),MONTH(TODAY()),1) VAR __FullYear = CALENDAR(__CurrentMonth, EOMONTH(__CurrentMonth,21)) RETURN FILTER(__FullYear,DAY([Date])=1)