How can I make my column chart to shift automatically right to left showing the current year data?

[Rck7]

Hi Friends,

I am working on my column chart where I need to display the current contract year months automatically shifting the past year values right to left, when ever a new contract year starts without using any Date slicers.

Note: My contract year starts & end from- [27/October to 26/October] every year.   

For example:

 The chart below, shows the data from 27/OCT/2016 - 26/OCT/2017. Once I load the data from 27/OCT/2017(i.e. in the coming weeks) in to my data table, I want my chart to shift the past year and show the monthly data for the current year starting 27/OCT/17 until 26/OCT/2018 and follow the same process for future years.

How would I be able to achieve it? 

Chart: 


 
File: https://www.dropbox.com/s/ehkkuluwobo121g/Fiscal_Year_Comparisions.pbix?dl=0

Any help would be appreciated!!!!!
Thank you.

[v-qiuyu-msft · Accepted solution]

Hi @Rck7,

You can create a measure below. It will filter UseageDate when it's greater or equal to the latest 10/27. Replace the chart value as this measure. You can see attached pbix file. 

Measure = var latestContract=CALCULATE(MAX('TokenRecords'[UsageDate]),FILTER(ALL(TokenRecords),MONTH('TokenRecords'[UsageDate])=10 && DAY('TokenRecords'[UsageDate])=27))
return
CALCULATE(SUM(TokenRecords[Tokens]),FILTER('TokenRecords','TokenRecords'[UsageDate]>=latestContract))

Best Regards,
Qiuyun Yu 

[Ashish_Mathur · Accepted solution]

Hi,

Try this

1. Drag UsageDate to Visual level filters
2. Select relative date filtering > in the last > 12 > calendar months > Apply Filter

Hope this helps.

[v-qiuyu-msft · Accepted solution]

Hi @Rck7,

You can create a measure below. It will filter UseageDate when it's greater or equal to the latest 10/27. Replace the chart value as this measure. You can see attached pbix file. 

Measure = var latestContract=CALCULATE(MAX('TokenRecords'[UsageDate]),FILTER(ALL(TokenRecords),MONTH('TokenRecords'[UsageDate])=10 && DAY('TokenRecords'[UsageDate])=27))
return
CALCULATE(SUM(TokenRecords[Tokens]),FILTER('TokenRecords','TokenRecords'[UsageDate]>=latestContract))

Best Regards,
Qiuyun Yu