How to display last 5 quarters based on current date

[Anonymous]

Hi Experts,

We have data that shows the pupulation increase for all the cities for all quarters.

In my report i have a slicer 'City'  and a line graph. The requirement is when we select a particular city from the slicer, then data only for last 5 quarters should be displayed in the Line graph.

Last 5 quarters from Current date.

Please advise how can we achieve this.

Many thanks.

[chane]

Hello, I found this solution is useful to show the last quarters. However, is there any method to update the last quarter compared to the current date? Thanks a lot! 

[v-danhe-msft · Accepted solution]

Hi @Anonymous,

Based on my test, you could refer to below formula to calculate the last quarter:

Sample data:

Create below calculated column:

Last quarter = IF(YEAR('Table'[Date])<MAXX('Table',YEAR('Table'[Date])),(MAXX('Table',YEAR('Table'[Date]))-YEAR([Date]))*4+5-[Quarter],[Quarter])

You could use this new column to create a slicer to show the last 5quarter:

You could also download the pbix file to have a view.

Regards,

Daniel He

[chane]

Hi Daniel, your solution is very useful! is there any method to update the last quarter compared to the current date? Thanks a lot! 

[Anonymous]

Thanks @v-danhe-msft .

Your solution helped me. Thanks again.

[Greg_Deckler]

See if my Quick Measure Time Intelligence The Hard Way will work for you: https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TIT... If you can post sample data and expected result then can be more specific.