cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
donaldo Regular Visitor
Regular Visitor

Next 12 months (DAX or M) table query

Hi,


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.

next 12 months.PNGI 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.

 

 

3 REPLIES 3
Community Support Team
Community Support Team

Re: Next 12 months (DAX or M) table query

Hi @donaldo.

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:

1.PNG

If I misunderstand you, could you please post me your desired result if possible?

 

Regards,

Daniel He

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
donaldo Regular Visitor
Regular Visitor

Re: Next 12 months (DAX or M) table query

Hi Daniel,


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.

Super User
Super User

Re: Next 12 months (DAX or M) table query

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)