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.
I am trying to subtract the previous row from the current row and save the results in another table.
As done in the image, I want to subtract the values on the previous date from the ones from the current date. I want to save it in another table.
Kindly help.
Solved! Go to Solution.
OK, for whatever reason, in my tests I can't get a calculated column expression to return anything but blanks.
However, I was able to create the measure above, calling it DiffPreviousDayMeasure, and then use this to create a column of correct values:
DiffColumn = [DiffPreviousDayMeasure]
I'll keep trying for a bit to see if I can get it to work without the intermediate measure, but I make no promises.
EDIT: OK, I figured out where I had the disconnect. I stumbled upon this, which gets close, in that it uses previous values to calculate the current value. During the creation of calculated columns, you can't use CALCULATE and a handful of other DAX expressions. It's already going row by row, so any reference to Table[Value] evaluates to the current value. In order to get the previous value, you need to create a var that uses a mix of MAXX to determine the biggest date prior to an EARLIER term. This seems like a lot of effort to get the syntax right, so I'm going to stick with my previous answer. Create this as a measure, and then set the calculated column equal to the measure.
I don't have an answer for you, just something to think about:
What happens when you re-order the data? Sort by Variable 1 instead of date? Would you want the whole thing re-calculated?
Then you could to do something like
VAR1 = SUBTRACT("Table 1"[Var 1],CALCULATE(SELECTEDVALUE("Table 1"[Var 1]),FILTER("Table1", [Date]=PREVIOUSDAY(EARLIER([Date]))))
Or if the data isn't guaranteed to be day by day and some time skips use this instead of the above FILTER:
LASTDATE(FILTER("Table1", [Date]<EARLIER([Date])))
What you're doing here is taking the every Var 1, then getting the previous date and using that Var 1 in the SUBTRACT statement. EARLIER here isn't talking about time, but rather context, so it returns the current value of Var1.
Note that I haven't tested this, so you'll have to play a little bit with the syntax to try and make it work.
That DAX code makes sense logically.
But it doesn't recognise the function SUBTRACT().
Is there any other way to do it?
Just use a minus sign instead of a function. It should work fine.
I am not able to figure out the correct syntax for that.
VAR1 = (SELECTEDVALUE("Table 1"[Var 1]) - CALCULATE(SELECTEDVALUE("Table 1"[Var 1]),FILTER(ALL("Table1"), [Date]=PREVIOUSDAY(EARLIER([Date]))))
It gives me a syntax error saying that:
"The first argument to 'PREVIOUSDAY' must specify a column."
If I remove the EARLIER keyword and simply write Table1[Date] = PREVIOUSDAY( Table1[Date]), It doesn't give any syntax error, but the columns are just blank.
Huh. PREVIOUSDATE works oddly. It takes a column of dates, not a single date. My mistake. Try VALUES instead of EARLIER in order to get the dates as a column of dates instead of a single date.
VAR1 = (SELECTEDVALUE(Table 1[Var 1]) - CALCULATE(SELECTEDVALUE(Table 1[Var 1]),FILTER(ALL(Table1), [Date]=PREVIOUSDAY(VALUES(Table1[Date]))))
Here's the table I was able to create with a small set of sample data:
In that case it gives a blank column.
Could there be any issue with the other table?
Wait, are you using a calculated column, or a measure? I set up my previous solution as a measure.
We're trying to create a calculated column.
It has to become a part of the table (data source) that we are using to create visuals.
OK, for whatever reason, in my tests I can't get a calculated column expression to return anything but blanks.
However, I was able to create the measure above, calling it DiffPreviousDayMeasure, and then use this to create a column of correct values:
DiffColumn = [DiffPreviousDayMeasure]
I'll keep trying for a bit to see if I can get it to work without the intermediate measure, but I make no promises.
EDIT: OK, I figured out where I had the disconnect. I stumbled upon this, which gets close, in that it uses previous values to calculate the current value. During the creation of calculated columns, you can't use CALCULATE and a handful of other DAX expressions. It's already going row by row, so any reference to Table[Value] evaluates to the current value. In order to get the previous value, you need to create a var that uses a mix of MAXX to determine the biggest date prior to an EARLIER term. This seems like a lot of effort to get the syntax right, so I'm going to stick with my previous answer. Create this as a measure, and then set the calculated column equal to the measure.
That works!
Thank you very much!
I want to find the difference between 2 consecutive dates.
So if the variable 1 is re-arranged, I would still like to see the difference between the values on 2 consecutive dates.
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 |
---|---|
106 | |
98 | |
75 | |
63 | |
62 |
User | Count |
---|---|
135 | |
105 | |
104 | |
80 | |
65 |