Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to Solution.
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
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
Thank you for your response. Here is the link to my sample data https://s3.amazonaws.com/abi-stats/sampledata.xlsx .
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..
@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.
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....
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.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
100 | |
76 | |
74 | |
49 |
User | Count |
---|---|
146 | |
108 | |
106 | |
90 | |
62 |