Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

DAX difference between quarter excluding Q1

Hi forum!

Note: I'm working in Excel 365.

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 Q1 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=YzWAcj

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.

4 Replies

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Icon for Super User rankSuper User
    4 years ago

    Hi,

    I am not sure how your data model looks like, but I tried to create a sample pbix file like below.

    The below is for creating a new column.

    I hope the below can provide some ideas on creating an accurate solution for your data model.

     

     

    Expected outcome CC =
VAR _quartercondition =
    QUARTER ( 'Data'[Quarter end date] )
VAR _yearcondition =
    YEAR ( 'Data'[Quarter end date] )
VAR _untilpreviousquartersameyear =
    SUMX (
        FILTER (
            data,
            QUARTER ( 'Data'[Quarter end date] ) = _quartercondition - 1
                && YEAR ( 'Data'[Quarter end date] ) = _yearcondition
        ),
        Data[Raw data]
    )
RETURN
    IF (
        _quartercondition = 1,
        Data[Raw data],
        Data[Raw data] - _untilpreviousquartersameyear
    )
    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Jihwan_Kim 

      Thanks for the response. I'm hoping for a DAX measure that I can later use on other meassures. Should have clarified that in the original post. Edited.

      I don't believe a column is the best option for me in this case. 

      I've also updated the file with a more complete data set and including the basic data model that I'm working on.

      Thanks