cancel
Showing results for
Did you mean:
Highlighted
Frequent Visitor

## Previous Quarter but only if same year

Hi there, I'm trying to convert from data that is in YTD format for every quarter, to doing a subtraction to get to the true QTD value/ But this subtraction should not be subtracting Q4 of the previous year to Q1 of this year.

I have this formula:

AmountQTD = Sum([Amount])-CALCULATE(Sum([Amount]),PREVIOUSQUARTER(dimPeriodDAX[Date]))

How do I modify it to only subtract amounts that reside in the same calender year?

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted Microsoft

## Re: Previous Quarter but only if same year

Hi @PuzzledSpark,

Let me check if my understanding is right. In table visual, you select Year_Quarter as row, Sum([Amount]) calculates the total amount of current quarter, the CALCULATE(Sum([Amount]),PREVIOUSQUARTER(dimPeriodDAX[Date])) display the previous Quarter amount. You want to calculate the difference total amount between Current Quarter and previous Quarter using the formula, right? For my sample table, For 2014 Q1(highlighted in yellow), the difference should be 4439, rather than 4439(2014 Q1)-4542((2013 Q4), isn't it? If it is, you should create a calculated column to get quarter column, then create a measure to get the difference, you should use the formula as follows.

```Quarter =CONCATENATE ( "Q", ROUNDUP ( MONTH ( [Date] ) / 3, 0 ) )Difference =IF ( FIRSTNONBLANK ( dimPeriodDAX[Quarter], 1 ) = "Q1", SUM ( [Amount] ), SUM ( [Amount] ) - CALCULATE ( SUM ( [Amount] ), PREVIOUSQUARTER ( dimPeriodDAX[Date] ) ))
```

In my sample table, I create a measure using the formula and get the following expected result.

```quarter-difference =
IF (
FIRSTNONBLANK ( 'Calendar'[Quarter], 1 ) = "Q1",
SUM ( Sales[SALE] ),
SUM ( Sales[SALE] )
- CALCULATE ( SUM ( Sales[SALE] ), PREVIOUSQUARTER ( 'Calendar'[Date] ) )
)
``` Please the three highlighted rows in my result.

Please let me know if you have any other issue.

Best Regards,

Angelia

4 REPLIES 4
Highlighted Solution Sage

## Re: Previous Quarter but only if same year

You don’t provide enough info to be 100% sure.  But try this:

`CALCULATE(Sum([Amount]),PREVIOUSQUARTER(dimPeriodDAX[Date]), MAX ( dimPeriodDAX[Year] )  )`

this assumes your calendar table has a ‘Year’ column.  I have a similar scenario but use a slicer for year and do this QTD calculations:

`TOTALQTD ( SUM ( Table[Amount] ) , PREVIOUSQUARTER( Calendar[Date] ), VALUES ( Calendar[Year] ) )`
Highlighted Microsoft

## Re: Previous Quarter but only if same year

Hi @PuzzledSpark,

Let me check if my understanding is right. In table visual, you select Year_Quarter as row, Sum([Amount]) calculates the total amount of current quarter, the CALCULATE(Sum([Amount]),PREVIOUSQUARTER(dimPeriodDAX[Date])) display the previous Quarter amount. You want to calculate the difference total amount between Current Quarter and previous Quarter using the formula, right? For my sample table, For 2014 Q1(highlighted in yellow), the difference should be 4439, rather than 4439(2014 Q1)-4542((2013 Q4), isn't it? If it is, you should create a calculated column to get quarter column, then create a measure to get the difference, you should use the formula as follows.

```Quarter =CONCATENATE ( "Q", ROUNDUP ( MONTH ( [Date] ) / 3, 0 ) )Difference =IF ( FIRSTNONBLANK ( dimPeriodDAX[Quarter], 1 ) = "Q1", SUM ( [Amount] ), SUM ( [Amount] ) - CALCULATE ( SUM ( [Amount] ), PREVIOUSQUARTER ( dimPeriodDAX[Date] ) ))
```

In my sample table, I create a measure using the formula and get the following expected result.

```quarter-difference =
IF (
FIRSTNONBLANK ( 'Calendar'[Quarter], 1 ) = "Q1",
SUM ( Sales[SALE] ),
SUM ( Sales[SALE] )
- CALCULATE ( SUM ( Sales[SALE] ), PREVIOUSQUARTER ( 'Calendar'[Date] ) )
)
``` Please the three highlighted rows in my result.

Please let me know if you have any other issue.

Best Regards,

Angelia

Highlighted
Frequent Visitor

## Re: Previous Quarter but only if same year

Thanks I tried this out but found the sub-totals are not correct. Am going to redirect this to my other post https://community.powerbi.com/t5/Desktop/Subtotal-not-correct/m-p/331061#M147994

Last column in screenshot below is what I got wiith your method. Tempted to mark it as the answer as technically it achieved the original ask but want to wait a little longer first. Highlighted Microsoft

## Re: Previous Quarter but only if same year

Hi @PuzzledSpark

The latest post is related the original thread above you post? It's confusing. Please create a new thread if you have new problem. One thread includes one issue here.

Best Regards,
Angelia

Announcements #### Power Platform Community Conference

Check out the on demand sessions that are available now! #### Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event. #### Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021 #### Get Ready for Power BI Dev Camp 