Show last 12 months from filtered date

[Onaiggac]

Im using directquery and I want a column chart that always shows the last 12 months.

I tryied to use something with DATEDIFF, MAX(DATE),... But with no success.

I have two tables, one is my Date table and the other with my data. And they are joined with date column.

[k-squared]

I was looking for something similar -- a chart that showed the last 12 months of data (NOT necessarily 12 months from today). First, I created a measure that finds all the distinct dates: 

Dates Distinct = DISTINCT(MYTABLENAME[MyDateField])

I then created a chart with MyDateField on the x axis and the value for the y axis. 

I then inserted a filter on MyDateField with the following settings (I'm using 64-bit version 2.103 from March 2022): 

Filter type: Top N

Show items: Top, 12

By value: Dates Distinct (the measure I created above).

Hope this helps.

[JayHerrera]

One way you could filter the data in the visual to the last 12 months..

1. Click the bar chart visual
2. In the Visualizations pane, under Filters click the date field

3. Change the Filter Type to Relative date filtering

4. Change to "is in the last 12 months" as shown in screenshot below

[VBVB]

💗this solution.

[omikeyz]

Thank you for this info. Is there a way to move January 2023 column AFTER December 2022?

[Tom_Y]

You may need extra columns in your Date Table for the sorting.

Make a "Month sorting order", then English Month Column sorted by that Column.

I copied the code form some youtube video.

[Onaiggac]

@JayHerrera its not work for me.

Its fixed to that range of dates. I want something more dynamic like, filter a Month and its return the last 12 months of that filtered month.

[v-ljerr-msft]

Hi @Onaiggac,

Here is a similar thread in which a good solution is mentioned. Could you go to check if it works in your scenario? 

Regards