5 Replies Latest reply on Sep 12, 2019 10:58 PM by Gousia Mulla

# Difference between first and last quarter

Hi All,

I want to calculate the difference between 'last quarter' and 'first quarter' for selected year@, if year selected in the filter is < current year.

For eg :

Q1     Q2     Q3     Q4

6        8        1        4

if you select year which is smaller than current year then it should display Q4-Q1 which is 4-6 = -2

If current year is selected,  it should display 'current quarter' - 'previous quarter' which is Q2-Q1 which is 8-6 = 2

Regards,

Gousia

• ###### 1. Re: Difference between first and last quarter

Hi,

Could you please attach some sample data in a packaged workbook?

Mahfooj

• ###### 2. Re: Difference between first and last quarter

Hi Mahfooz,

Attaching the sample data.

Regards,

Gousia

• ###### 3. Re: Difference between first and last quarter

Is it something like this?

Sales Growth (CQ vs. PQ):

IF YEAR([Order Date])={MAX(YEAR([Order Date]))} AND DATEPART('quarter', [Order Date])={MAX(DATEPART('quarter', [Order Date]))}

THEN ({SUM({FIXED DATEPART('quarter', [Order Date]):AVG(IIF(DATEPART('quarter', [Order Date])={MAX(DATEPART('quarter', [Order Date]))},[Sales],NULL))})}

-

{SUM({FIXED DATEPART('quarter', [Order Date]):AVG(IIF(DATEPART('quarter', [Order Date])={MIN(DATEPART('quarter', [Order Date]))},[Sales],NULL))})})

/{SUM({FIXED DATEPART('quarter', [Order Date]):AVG(IIF(DATEPART('quarter', [Order Date])={MIN(DATEPART('quarter', [Order Date]))},[Sales],NULL))})}

END Based on your confirmation, I'll proceed further!

Mahfooj

• ###### 4. Re: Difference between first and last quarter

You are trying to display the percentage. I have to display the value as it is.

Till now I was just able to remove the value of Q4 and Q1. But when I try to subtract it (Q4-Q1) it displays NULL.

I used following function for Q4 and Q1 value :-

Q1 =

IF MAX(DATEPART('quarter', [Planning Folder])) = 2

THEN [Non-Compliance]

END

Q4 =

if MIN(DATEPART('quarter', [Planning Folder])) = 1

THEN [Non-Compliance]

END

where my fiscal year starts from April

• ###### 5. Re: Difference between first and last quarter

I am getting values for first and last quarter using FISRT() and LAST() function. But when I perform subtraction between those 2 values it gives 0