Forum Discussion
How to display last 5 quarters based on current date
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.
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
5 Replies
v-danhe-msftMicrosoft Employee
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
Hi Daniel, your solution is very useful! is there any method to update the last quarter compared to the current date? Thanks a lot!
- Greg_Deckler
Community Champion
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-TITHW/m-p/434008 If you can post sample data and expected result then can be more specific. 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!
