Calculate last 4 Q cummulative Sum of sales

[BItoken]

Hi guys,

I have last 12 Qtrs in my Power BI report and i have to add a column to show last 4 quarters cummulative sum 

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 help is much appreciated

[amitchandak]

@BItoken , Make sure you move Qtr to new table, or it is in your date table.

Create a Rank on QTR

Qtr Rank = RANKX(All(Date), Date[FY QTR],,asc,dense)

Try like

Avg last 4 QTR =
calculate(Average(Table[sales]),filter(Date, Date[Qtr Rank]>= Max(Date[Qtr Rank])-4 && Date[Qtr Rank]<=max(Date[Qtr Rank])))

If you have date

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

To 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/

[BItoken]

I get this error when i try to create a rank on the Qtr number or the FYQ column

A single value for column 'fiscalQ' in table 'TABLE' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.

Any Suggestions @amitchandak 

[v-lionel-msft]

Hi @BItoken ,

Has your problem been solved?

Is this what you want?

You can refer to this formula.

Measure = 
CALCULATE(
    SUM(Sheet1[Sales]),
    FILTER(
        ALLSELECTED(Sheet1),
        Sheet1[Index] <= SELECTEDVALUE(Sheet1[Index]) 
    )
)

If not, please give me a sample data model and tell me what effect you want to get.

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[BItoken]

Hi @v-lionel-msft 

Thanks for your reply

My Desired output is as below. Your logic gets me the rolling sum for all the quarters

I need the sum for last 4 qtrs and then reset for every next qtr

[v-lionel-msft]

Hi @BItoken ,

Try to do like this:

1. Add a [Index] column in "Edit Queries";

2. Create a measure;

Measure = 
VAR x = 
CALCULATE(
    SUM(Sheet1[Sales]),
    FILTER(
        ALL(Sheet1),
        Sheet1[Index]<= SELECTEDVALUE(Sheet1[Index]) && Sheet1[Index] >= SELECTEDVALUE(Sheet1[Index]) - 3 
    )
)
RETURN
IF(
    SELECTEDVALUE(Sheet1[Index]) in {1, 2, 3},
    BLANK(),
    x
)

My sample data comes from the same table. If your columns come from different tables, the formula will change.

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[BItoken]

I have the Date and FYQ in the same table and FY 21 Q1 is our current Qtr started Apr 1 2020.

I tried this and i think it will not work 

Quarter Rank = RANKX(all('QTR'),'QTR'[fiscalqtd],,ASC,Dense)

My FY data set is like this below.