Date dimension for last 12 months

[pade]

Maybe I'm atacking the problem in an incorrect way, please give feadback on that as well

I'm trying to make it easy for users of a data set to present diagrams of measures in a variation of date scales. I.E. I would like to make it easy for users to create diagrams that shows the last 6 month of data, the last 12 month of data etc

What I did was to create a new Date dimention table (Date Last 12 Months Scale) that users could use as e.g. an X-axis date matrix

The intension is that the users creating reports should be able to use the "Date Last 12 Months Scale" date dimension to create a diagram that always shows the last 12 months (or last 6 month by using an Date Last 6 Months Scale). The date Date dimention I created using: Date 12 months scale = DATESINPERIOD('Date'[Date];TODAY();-12;MONTH)

There's some problem with this method. The Date dimension is only freated when the data set is loaded. If I use daily refresh, that would not be a problem though. But the other problem is that the Date Dimension only will contain the days missing in current month. I.E. if I created the new Date table the 20'th of June, the table will only contain dates from the 20'th of July-16 to the 20-th of June-17. This is not correct as I would like to have whole months

Anyone that understands what I would like to do and have a good solution?

[Chihiro · Accepted solution]

Try using CALENDAR function with EOMONTH.

= CALENDAR(DATE(YEAR(TODAY())-1,MONTH(TODAY())+1,1),EOMONTH(Today(),0))

This creates table with dates from 7/1/2016 to 6/30/2017.

[pade]

Thanks a lot @Chihiro.

Although I slightly changed the code to: CALENDAR(DATE(YEAR(TODAY()),MONTH(TODAY())-12,1),EOMONTH(Today(),0))

This way it will be easier to change between different periods, and the year will be correct calculated anyway, no matter if I write -24, -12, or -6

Best Regards and thanks for supporting //Pär