topic Re: DAX difference between quarter excluding Q1 in DAX Commands and Tips

DAX difference between quarter excluding Q1

Anonymous — Thu, 23 Jun 2022 16:01:04 GMT

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.

Re: DAX difference between quarter excluding Q1

Jihwan_Kim — Thu, 16 Jun 2022 03:02:02 GMT

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 )

Re: DAX difference between quarter excluding Q1

Anonymous — Thu, 16 Jun 2022 14:31:23 GMT

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

Re: DAX difference between quarter excluding Q1

Anonymous — Tue, 21 Jun 2022 08:26:46 GMT

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

Re: DAX difference between quarter excluding Q1

Anonymous — Thu, 23 Jun 2022 16:01:44 GMT

Hi Anonymous

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

Thanks