Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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

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

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.

@v-cherch-msft

 

Thank you for your response. Here is the link to my sample data https://s3.amazonaws.com/abi-stats/sampledata.xlsx . 

 

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

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

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

 

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

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.

@v-cherch-msft @venug20

 

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.