DAX difference between quarter excluding Q1

Shinka 131 Reputation points

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.



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,338 questions
{count} votes

2 answers

Sort by: Most helpful
  1. Herbert Seidenberg 1,106 Reputation points

    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.

  2. Emi Zhang-MSFT 10,526 Reputation points Microsoft Employee

    Hi @Shinka ,
    Try this formula:



    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.

    No comments