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 fo...
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

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Hi Anonymous ,

       

      //This item may not exist or is no longer available.

       

      Would you mind sharing the file again? Thanks in advance.

      How to provide sample data in the Power BI Forum

       

      Best Regards,
      Gao
      Community Support Team

       

      If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

      How to get your questions answered quickly -- How to provide sample data

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        Hi Anonymous 

        It had an expiration date. Reposted with expiration June 30.

        Thanks