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.
Hi,
I need help in order to solve a problem trying to calculate the differences between two rows in Power BI.
My tables look like:
Date Orders Index
01/01/2017 8501 1
02/01/2017 8345 2
03/01/2017 7985 3
04/01/2017 8134 4
I would need to performt the difference betweent the rows of Order in order to get something like:
Difference
156 (from 8501-8345)
360 (from 8345-7985)
-149 (from 7985-8134)
I've tried to add a column with:
=Orders{Index}-Orders{Index+1}
but it does not work.
I will appreciate any help. Thanks
Solved! Go to Solution.
Hi @SSS
Using DAX you can add this calculated column to get desired results
= VAR NextIndex = Table1[Index] + 1 RETURN Table1[Orders] - CALCULATE ( VALUES ( Table1[Orders] ), FILTER ( ALL ( Table1 ), Table1[Index] = NextIndex ) )
I have a similar problem.
I have 2 different datasets (for example sake, let's say volume table and ranking table). In the volume dataset, I have a measure that calculates the sum of the volume for each material based on an indicator column if it was received or issued. In the ranking table, the plant, material, plant volume limit, and the plant volume limit change date. I created a column in the ranking table called ranking that gives a numerical rank of the oldest plant volume limit change date from earliest (rank 1) to the latest for each material at each plant. I would like to accomplish 2 things:
Here is the ranking table
Hi,
Your question is not at all clear. Start a new thread and post your question there.
I have another case. I would like to calculate the difference between rows. For example for 10-22
77-73=4
73-67=6
67-59=8
etc...
Hello Guys,
I have the same question on how to find the difference between two rows from the dated column. Below is how my table looks.
Date | Quantity | Diff |
06/01/2020 | 1 | |
06/02/2020 | 3 | 2 |
06/03/2020 | 6 | 3 |
I just want the difference between the quantity column. I have tried multiple queries but it works fine in Import Mode but mine is Direct Query mode. Looks like there are many limitations in Direct Query. Could someone help me in giving some guidance regarding this for DIrect Query?
Thanks in advance.
Hi,
You should have a Calendar Table with a relationship from the Date column of your Data Table to the Date column of your Calendar Table. To your visual, drag the Date column from the Calendar Table. Write these measures:
Quantity = sum(Data[Number])
Quantity on previous day = calculate([quantity],previousday(calendar[date]))
Diff in quantity = [quantity]-[Quantity on previous day]
Hope this helps.
Thank you. It worked
You are welcome. If my reply helped, please mark it as Answer.
Hey guys,
I encountered one problem I simply can't solve. If anyone can help me out I would be extra grateful!
I want to calculate the difference between two rows, but after I import data to the data model, the sort order is mixed up (see below: Report Date and Account Name) so one of my formulas that could work if the sort order is correct doesn't work.
The table looks like this and the Change column like this is my goal.
Account Name | MRR | Report Date | Change |
a | 100 | 1/31/2017 0:00 | 100 |
a | 100 | 2/28/2017 0:00 | 0 |
a | 150 | 4/30/2017 0:00 | -30 |
a | 100 | 5/31/2017 0:00 | -50 |
b | 13 | 7/31/2019 0:00 | 13 |
b | 13 | 8/31/2019 0:00 | 0 |
c | 5 | 7/31/2019 0:00 | 5 |
a | 100 | 6/30/2017 0:00 | 0 |
a | 100 | 7/31/2017 0:00 | 0 |
a | 100 | 8/31/2017 0:00 | 0 |
a | 100 | 9/30/2017 0:00 | 0 |
a | 100 | 10/31/2017 0:00 | 0 |
a | 180 | 3/31/2017 0:00 | 80 |
You can notice that the account doesn't have the correct order either by the report date.
If there is any way how to solve it (maybe as a measure? or maybe is there a way how to correctly import data?)
I would be so so grateful.
All the best,
Tomislav
Hi,
You may download my PBI file from here.
Hope this helps.
Hi, I have almost the same data but the column MRR includes blanks, I appreciate your advice to let me know what the formula will look like? i need if the column value is blank to take the earliest value found instead of calculating based on zeros instead of balnks. Thanks.
Hi,
Refer to column1 of the image. You will see that the result in the 5th row is not 100-0=100 but 100-180=-80. You may download my PBI file from here.
Hope this helps.
Hi Ashish, thanks for your support. appreciated.
your solution works fine for the import data but i have direct query data so i cannot add a custom column to do what you adviced, i believe i need the same formula on the power query. i appreciate your support. thanks.
You are welcome. Someone else will help you with that.
Thank you @Ashish_Mathur !
Works amazing!
Thank you so much.
All the best from Croatia!
Tomislav
You are welcome.
Thanks Guys!! It really helped!
Hi @SSS,
If you have a calendar table and there is a relatioship from the date column of your base data table to the date column of the calendar table, then you can use this calculated field formula
=CALCULATE(SUM(Data[Orders]),PREVIOUSDAY(Calendar{Date]))-SUM(Data[Orders])
@Anonymous
You can use one of these...I believe
Days from Next Date = VAR Next_Date = MINX ( TOPN ( 1, FILTER ( Table1, [Key] = EARLIER ( [Key] ) && [Date] > EARLIER ( [Date] ) ), [Date], ASC ), [Date] ) RETURN DATEDIFF ( [Date], Next_Date, DAY )
OR
Days from Previous Date = VAR Previous_Date = MINX ( TOPN ( 1, FILTER ( Table1, [Key] = EARLIER ( [Key] ) && [Date] < EARLIER ( [Date] ) ), [Date], DESC ), [Date] ) RETURN DATEDIFF ( Previous_Date, [Date], DAY )
Hi @Zubair_Muhammad,
Thanks a lot!
The first one result a strange column, but the second works when I compared with Excel result.
Now, I'll proceed to tests accessing real data from the database.
Dax has some mysteries to me, yet. I didn't know the VAR concept or the possibility to call previous or next records in an instruction.
Thanks a lot again.
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 |
---|---|
110 | |
94 | |
81 | |
66 | |
58 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |