Forum Discussion
Previous Quarter Calculation
Hi Great Minds,
So to start with I am having a Quarter Field which I derived from Month and Year field.
SO the Quarter Column formating like this:
FY20 Q4
FY20 Q3
FY20 Q1
..............so on
Now my requirement is to get the Sum of Sales for Current and Previous four quarters in a KPI/Card.
For current Sales I am getting the result using this DAX:
Total sales_current quarter = CALCULATE(SUM(Table1[sale]),FILTER(ALLSELECTED(Table1), Table1[Quarter] =MAX(Table1[Quarter])))
However, for the previous and last to previous quarters I am not able to derive. Can you please help me out?
Shaji
Seems you already have this Quarter Column, then you can create previous qtr and last to previous qtr with the following dax:Previous Quarter = CALCULATE(SUM([Sales]),FILTER(ALLSELECTED('Table'),'Table'[Quarter]=MAX('Table'[Quarter])-1)) Last to Pre Quarter = CALCULATE(SUM([Sales]),FILTER(ALLSELECTED('Table'),'Table'[Quarter]=MAX('Table'[Quarter])-2))Please see attached sample pbix if needed.
Paul Zheng _ Community Support Team
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 Replies
Shaji - There is a PREVIOUSQUARTER function I believe. Otherwise, You may find this helpful - https://community.powerbi.com/t5/Community-Blog/To-bleep-With-Time-Intelligence/ba-p/1260000
Also, see if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.
https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TITHW/m-p/434008Shaji , If you have date or you can create date from month year
date = date([year],[month],1) //format number
date = "01-" & [Month] & "-" [Year] // Jan, 2020
You can now use time intelligence and date table
QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(('Date'[Date]))) Last QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(dateadd('Date'[Date],-1,QUARTER))) Last complete QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD( ENDOFQUARTER(dateadd('Date'[Date],-1,QUARTER)))) Last to last QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(dateadd('Date'[Date],-2,QUARTER))) Next QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(dateadd('Date'[Date],1,QUARTER))) Last year same QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(dateadd('Date'[Date],-1,Year))) Last year same QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(ENDOFQUARTER(dateadd('Date'[Date],-1,Year))))Power BI — QTD - With or without time intelligence
https://medium.com/@amitchandak.1978/power-bi-qtd-questions-time-intelligence-2-5-d842063da839To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/
See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-YTD-LYTD-Week-Over-Week/m-p/1051626#M184
Appreciate your Kudos.Shaji
Seems you already have this Quarter Column, then you can create previous qtr and last to previous qtr with the following dax:Previous Quarter = CALCULATE(SUM([Sales]),FILTER(ALLSELECTED('Table'),'Table'[Quarter]=MAX('Table'[Quarter])-1)) Last to Pre Quarter = CALCULATE(SUM([Sales]),FILTER(ALLSELECTED('Table'),'Table'[Quarter]=MAX('Table'[Quarter])-2))Please see attached sample pbix if needed.
Paul Zheng _ Community Support Team
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
