Urgent - Trailing 4 quarters Sum

[BItoken]

Hi Everyone,

I have last 12 Qtrs in my Power BI report and i have to add a column to show Trailing 4 quarters sum running until the current qtr

For example i need

Sum of FY19-Q1,Q2,Q3,Q4 at FY-19Q4

Sum of FY19-Q2,Q3,Q4,FY20-Q1 at FY20-Q1

Sum of FY19 Q3,Q4,FY20-Q1,Q2 at FY20-Q2

and so on

Any Suggestions... Tried Rank on quarters but does not help

[Ashish_Mathur]

Hi,

Is your input table, the first 2 columns?  If yes, then share the data in a form that can be pasted in an MS Excel file.  Which months fall in Q1?

[BItoken]

Yes first 2 columns are coming from my Data tables.

Apr to June is Q1

So ex: Apr 1 2020 to June 30 2020 is FY 21 Q1

FQ	Sales	Trailing 4 Qtrs
FY19-Q1	40
FY19-Q2	50
FY19-Q3	70
FY19-Q4	60	220
FY20-Q1	40	220
FY20-Q2	80	250
FY20-Q3	100	280
FY20-Q4	200	420
FY21-Q1
FY21-Q2
FY21-Q3
FY21-Q4

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.  Let me know if you are OK with this.  If you want the first 3 rows to definitely show a blank, then post back and i will modify my formula.

Hope this helps.

[amitchandak]

create an ascending rank on Qtr (In case you do not have Date)

Make sure Your Qtr in in Different table

Qtr Rank = RANKX(all('Date'),'Date'[Qtr Start date],,ASC,Dense)

Or

Qtr Rank = RANKX(all('Date'),'Date'[Qtr name],,ASC,Dense)

Last 4week Qtr = CALCULATE(SUM(Sales[Net Sales]),FILTER(all('Date'),'Date'[Week Qtr ]>=min('Date'[Week Qtr ])-4 && 'Date'[Week Qtr ]<=max('Date'[Week Qtr ])))

This Qtr = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Qtr ]=max('Date'[Week Qtr ])))
Last Qtr = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Qtr ]=max('Date'[Week Qtr ])-1))

In case you have Date , prefer a date table and rolling

Rolling 4 Qtr  = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD(Table[Date],ENDOFMONTH(Sales[Sales Date]),-4,QUARTER))

https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-Y...

[BItoken]

Hi,

I already created a Rank on the Qtr table which is a separate table

Also i created  a measure on this rank 

trailing 4 Qtrs =  CALCULATE(SUM([Sales]),

FILTER(Qtr, Qtr[Rank] >= MIN(Qtr[Rank])-4 && Qtr[Rank] <= MAX(Qtr[Rank])))

It just gets the data for sales per Qtr (Nothing happens)