cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
kodalisatish
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.sample data.png

 

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 v-cherch-msft
Microsoft

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

Hi @kodalisatish

 

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.

View solution in original post

8 REPLIES 8
Microsoft v-cherch-msft
Microsoft

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

Hi @kodalisatish

 

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.

 

1.png

 

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.
kodalisatish
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 https://s3.amazonaws.com/abi-stats/sampledata.xlsx . 

 

venug20 Resolver I
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

 

 

Sample-QOQ.jpg

 

 

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

Highlighted
kodalisatish
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.

venug20 Resolver I
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....

 

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

 

Thank you for your time.

Microsoft v-cherch-msft
Microsoft

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

Hi @kodalisatish

 

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.

View solution in original post

kodalisatish
Frequent Visitor

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

@v-cherch-msft @venug20

 

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

Helpful resources

Announcements
New Ranks Launched March 24th!

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!

‘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

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.

Join THE global Microsoft Power Platform event series.

Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors