cancel
Showing results for
Did you mean:
Frequent Visitor

## Difference between two rows based on date (quarters)

Hello All,

I am trying to get difference between two rows based on time(quarter) Below is my sample data.

I want my value2 to be the value from previous quarter. I created a new measure using the following DAX query, but it is not working as expected.

Value2 = CALCULATE([Value1], DATEADD('Total By Quarter'[Date],-1,QUARTER))

I am getting balnk values in value2. I am not sure if the issue is with the date field format or something else.

Any help is appreciated.

1 ACCEPTED SOLUTION

Accepted Solutions
Microsoft

## Re: Difference between two rows based on date (quarters)

You may try to use below measure when you use Date instead of Date Hierarchy.

```Measure =
VAR PQ =
CALCULATE (
SUM ( 'Total By Quarter'[Values] ),
DATEADD ( 'Total By Quarter'[Date], -1, QUARTER )
)
RETURN
SUM ( 'Total By Quarter'[Values] ) - PQ```

Regards,

Cherie

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
8 REPLIES 8
Microsoft

## Re: Difference between two rows based on date (quarters)

Based on my test as below, it seems the dax is correct. You may try to check the relationships or date hierarchy in your data. If it is not your case, please share some data sample. You can upload it to OneDrive or Dropbox and post the link here.

Regards,

Cherie

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Frequent Visitor

## Re: Difference between two rows based on date (quarters)

@v-cherch-msft

Thank you for your response. Here is the link to my sample data

Resolver I

## Re: Difference between two rows based on date (quarters)

@kodalisatish

Pls workout this formula....

QOQ = VAR PQ = CALCULATE(SUM('Total By Quarter (2)'[Values]), DATEADD('Total By Quarter (2)'[Date].[Date], -1, QUARTER))
Return
SUM('Total By Quarter (2)'[Values]) - PQ

If it is solution for this query... Pls accept as solution..

Frequent Visitor

## Re: Difference between two rows based on date (quarters)

@venug20 Can you tell me why we have to use 'Total By Quarter (2)'[Date].[Date] instead of 'Total By Quarter (2)'[Date] ?

Also while calculating the difference, negative numbers are showing as postive numbers in the measure. Is there a way to show negative numbers in the measure?

Thank you for your help with this.

Resolver I

## Re: Difference between two rows based on date (quarters)

@kodalisatish

As per my knowledge "'Total By Quarter (2)'[Date].[Date]" has taken upto end of the quater date. Total By Quarter (2)'[Date] has taken upto your last transaction date of quarter date.

Pls share your "PBIX" file... where it is going wrong.. i will check and give reply to you....

If it is solution for you... pls accept as solution....

Highlighted
Frequent Visitor

## Re: Difference between two rows based on date (quarters)

@venug20

I apologize for the delayed response. Here is the link to the pbix file https://s3.amazonaws.com/abi-stats/test.pbix

There are two charts in it.

First one is with Date and Values. Second one is where I am trying to build it similar to the first one but with using Difference as values. When I do that it is showing only per year instead of quarter. When I change the Date format to Date instead of Date Hierarchy it not showing any data for the years.

I am not sure why it is not working. Any help is appreciated.

Microsoft

## Re: Difference between two rows based on date (quarters)

You may try to use below measure when you use Date instead of Date Hierarchy.

```Measure =
VAR PQ =
CALCULATE (
SUM ( 'Total By Quarter'[Values] ),
DATEADD ( 'Total By Quarter'[Date], -1, QUARTER )
)
RETURN
SUM ( 'Total By Quarter'[Values] ) - PQ```

Regards,

Cherie

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Frequent Visitor

## Re: Difference between two rows based on date (quarters)

Thank you both for your help with this one. Very much appreciate it.

Announcements

#### New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

#### ‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

#### Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Top Solution Authors
Top Kudoed Authors