Possible for a line chart to show a rolling 12 months where current month is always at the right?

[JClishe]

I have a line chart with a visual level filter set to show the last 12 months, and it works.

However, the chart goes from left to right, January to December. It's currently February as I'm writing this which means if I want to visualize, say, the last 4 months of data I have to start by looking at Feb and Jan on the left side of the chart, then I need to jump over to the right side of the chart to see Dec and Nov.

I'd like for the current month to always be on the far right and the previous months trail from right to left. Is this possible?

[v-yuta-msft]

Hi JClishe,

To achieve your requirement, you can use DAX formula below:

Value in last 12 month = CALCULATE(SUMX(Table1, Table1[Value]), DATESBETWEEN(Table1[Date], EDATE(TODAY(), -12), TODAY()))

The result is as follow and you can refer to the PBIX file: https://www.dropbox.com/s/1bk26x2oue1kg87/Case14.pbix?dl=0

 

Best Regards,

Jimmy Tao

[JClishe]

This is probably a silly question but how do you get the DAX formula bar to appear on a visualization? 

[v-yuta-msft]

Hi JClishe,

As @mussol_ mentioned, select measure in the fileds bar then make a screen shot.

In addition, to create a measure, right click on the table then click “new measure”.

Regards,

Jimmy Tao

[JClishe]

I'm sorry but I'm still not following. It doesn't make any sense to me that the way to make the formula bar appear is to take a screen shot so our lines must be getting crossed somewhere.

Here's where I'm at...I have a line chart already created and it has all the data I want. January is on the left and December is on the right. I want it to be a rolling 12 months where the current month is on the right and the months trail off right to left. The DAX formula that's been posted earlier appears to be exactly what I'm looking for. So I select my line chart and.....then what? I don't see a formula bar and I've gone through all the options in the ribbon, I can't figure out how to make the formula bar appear. 

[v-yuta-msft]

Hi JCishe,

Sorry for late, to create a measure:

1.Right click on the table in fields then click new measure like below:

2.Rename the measure and input DAX formula, then click “enter” button.

3.Now the measure has been created, if you want to see formula in the measure, click on it.

Best Regards,

Jimmy Tao

[JClishe]

OK...I was able to create the measure, thanks for that! 

I added the new measure to my chart and I removed the old measure. But the chart is still sorting the same way...Jan on the left and December on the right. It isn't displaying as a rolling 12 months. Is there something else I need to do to change the charts sorting options? I've been looking around and don't see anything, everything looks exactly the same as it did before I created this new measure.

Below is a screenshot of the chart, and the formula for the measure that I'm using as the value.

[v-yuta-msft]

Hi JClishe,

Did you change the date hierarchy like deleting the year level?

My date table hierarchy is like below:

After I deleted Year and Day level, the result is similar with yours:

Please use date hierarchy instead of month only.

Regards,

Jimmy Tao

[JClishe]

When I use the entire date hierarchy it totals by year instead of month. Thoughts?

[v-yuta-msft · Accepted solution]

Hi JClishe,

Please click button “Expand all down one level in the hierarchy” in the left upper of visualization.

After expanding the hierarchy:

 

Regards,

Jimmy Tao

[JClishe]

That did it, thanks! 

[Ashish_Mathur]

Hi,

Share the link from where i can download your file.

[mussol_]

Nothing is a silly question!

Just click on measure on the right hand bar and make an screen capture!

Regards.

Josep.