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