cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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
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? 

 

1.PNG


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

2.PNG

Please the three highlighted rows in my result.

Please let me know if you have any other issue.

Best Regards,

Angelia

View solution in original post

4 REPLIES 4
Highlighted
Solution Sage
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
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? 

 

1.PNG


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

2.PNG

Please the three highlighted rows in my result.

Please let me know if you have any other issue.

Best Regards,

Angelia

View solution in original post

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.

 

Untitled.png

 

Highlighted
Microsoft
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

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Upcoming Events

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors