Previous Quarter but only if same year

[PuzzledSpark]

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?

[v-huizhn-msft · Accepted solution]

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

[PuzzledSpark]

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.

[v-huizhn-msft]

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

[mattbrice]

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] ) )