DAX difference between quarter excluding Q1

Shinka 131 Reputation points
2022-06-15T14:09:43.103+00:00

Hi forum!

I have a time series sliced by quarter but each quarter is cumulative. So Q1 is sales data for Q1, but Q2 is sales data for Q1+Q2, in Q3 it's sales data for Q1+Q2+Q3, and for Q4 it's sales data for Q1+Q2+Q3+Q4. This data is given to me like this and I cannot ask for it to be otherwise.

Thing is, I need the value for each quarter, not cumulative.

I've been racking my brain around in DAX for Power Pivot and I've managed to substract the quarter to get each value; thing is it does the same for Q1. It's substracting the value of Q4, cumulative, for Q one so the output for Q1 is rubbish.

I need to find a way of substracting the values for each quarter except when it's Q1.

Is there anyway to do this?

Below a simple example file of what I need.

https://1drv.ms/x/s!AqEBYAVXS3ejg6VA8J0p_yPhbcDrHw?e=GkJyyD

Thanks!

EDIT:
I'm hoping for a DAX measure that will return the Expected out in the sample file. I intend to use this measure in other measure later on.
I've updated the file linked above with a more complete version of the data model I'm using for a better reference.

Excel Management
Excel Management
Excel: A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data.Management: The act or process of organizing, handling, directing or controlling something.
1,638 questions

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Herbert Seidenberg 1,191 Reputation points
    2022-06-16T02:36:33.177+00:00

    Excel 365 Pro Plus with Power Pivot and Power Query.
    Transform cumulative amounts to quarterly additions.
    Starting with zero amount at beginning of each year.
    https://www.mediafire.com/file/t7jntlyhpgearms/06_15_22.xlsx/file
    https://www.mediafire.com/file/vq3dw5s2lb7zs0b/06_15_22.pdf/file

  2. Emi Zhang-MSFT 21,626 Reputation points Microsoft Vendor
    2022-06-16T06:24:35.55+00:00

    Hi @Shinka ,
    Try this formula: 

    =IF(RIGHT(A2,2)="Q1",B2,IF(RIGHT(A2,2)="Q2",B2-SUMPRODUCT(($A$2:$A$18=LEFT(A2,5)&1)*$B$2:$B$18),IF(RIGHT(A2,2)="Q3",B2-SUMPRODUCT(($A$2:$A$18=LEFT(A2,5)&2)*$B$2:$B$18),IF(RIGHT(A2,2)="Q4",B2-SUMPRODUCT(($A$2:$A$18=LEFT(A2,5)&3)*$B$2:$B$18),""))))

    211925-image.png

    If the response is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    0 comments