Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
donaldo
Helper III
Helper III

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
v-danhe-msft
Employee
Employee

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.

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.

Stachu
Community Champion
Community Champion

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)


Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.