Forum Discussion
MAT Based Previous Quarters
| vl_sales | year | quarter | Categoty |
| 113mi | 2021 | 1 | Kit Kat |
| 114mi | 2021 | 2 | Nescau |
| 100mi | 2021 | 3 | Chocolate |
| 87mi | 2021 | 4 | Nescau |
| 88mi | 2022 | 1 | Beve |
| 115mi | 2022 | 2 | Iorgurt |
| 77mi | 2022 | 3 | Nesquit |
| 100mi | 2022 | 4 | Nescau |
| 90mi | 2023 | 1 | Cookies |
dim_calendar
| year | quarter | quarter_list |
| 2021 | 1 | 202101 |
| 2021 | 2 | 202102 |
| 2021 | 3 | 202103 |
| 2021 | 4 | 202104 |
| 2022 | 1 | 202201 |
| 2022 | 2 | 202202 |
| 2022 | 3 | 202203 |
| 2022 | 4 | 202204 |
| 2023 | 1 | 202301 |
| 2023 | 2 | 202302 |
| 2023 | 3 | 202303 |
| 2023 | 4 | 202304 |
How can I calculate Moving Annual Sales for previous 4quarters, in way that 2023 Q1 = sum of values of the lasts 4 quarters,
and 2022 Q4 = sum of values of the lasts 4 quarters, and so on? Any Help?
2 Replies
- amitchandak
Super User
MacedoLuis , Create a new Rank column in dim calendar (calling it date in my formula)
Qtr Rank = RANKX(all('Date'),'Date'[quarter_list],,ASC,Dense)
Then you can have measure like
This Qtr = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Qtr Rank]=max('Date'[Qtr Rank])))
Last Qtr = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Qtr Rank]=max('Date'[Qtr Rank])-1))
Time Intelligence, Part of learn Power BI https://youtu.be/cN8AO3_vmlY?t=27510
Time Intelligence, DATESMTD, DATESQTD, DATESYTD, Week On Week, Week Till Date, Custom Period on Period,
Custom Period till date: https://youtu.be/aU2aKbnHuWs&t=145sPower BI Custom Period Till Date (PTD)- https://youtu.be/rQ3Z_LtxwQM
amitchandak I tried it, and got blanks. And also tried like this, but i got blanks. Would you have another to calculate it?
fx_MovingAnnualTotal =VAR fx_calendartable =SUMMARIZE(DimCalendario,DimCalendario[Data],DimCalendario[Quarter],DimCalendario[Trim_Ano],"@Qtr Rank",RANKX(ALL(DimCalendario), DimCalendario[Trim_Ano],,ASC,Dense))VAR fx_LastQtr =CALCULATE([Faturamento na Venda], FILTER(fx_calendartable, [@Qtr Rank] = MAXX(fx_calendartable, [@Qtr Rank])-1))RETURNfx_LastQtr
