Forum Discussion
Subtract per Quarter
Hi Everyone,
After some searching and getting lost in the DAX, I would like some help / reccomendations.
I have the table (Table1) below; where Quarter and Year is a calculated column.
I would like to create a bar graph that subtracts the total number of assets in each quarter from the previous quarter.
For this example I have a total of 12 assets which I was able to sum by using
Total Assets =
But how do I subtract one quarter from the pervious?
In the end, I would have a bar graph that is shown in red in the bottom right of this page.
Thanks!
2 Replies
- amitchandak
Super User
Anonymous , If you have date then you can use time intelligence. If not you need a separate table with Qtr year and related column. You need a Rank on YYYYQ (Year Qtr) to get QOQ
example with Time Intelligence
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 QUARTER Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD( ENDOFQUARTER(dateadd('Date'[Date],-1,QUARTER))))
Last QUARTER Sales = CALCULATE(SUM(Sales[Sales Amount]),PREVIOUSQUARTER(('Date'[Date])))Option 2 :
New column In Qtr Year Table
Qtr Year = year([Date]) & [Qtr]
Qtr Rank = RANKX(all('Date'),'Date'[Qtr Year ],,ASC,Dense)measure
This Qtr = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Qtr Rank]=max('Date'[Qtr Rank])))
Last Qtr = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Qtr Rank]=max('Date'[Qtr Rank])-1))Power BI — Qtr on Qtr with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-qtd-questions-time-intelligence-2-5-d842063da839
https://www.youtube.com/watch?v=8-TlVx7P0A0
