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'd like to address a certain issue I am facing w/ Power BI.
I have a table in the following format (date format dd/mm/yyyy):
date | value1 | value2 | value3 |
24/04/2020 | 4 | 1 | 6 |
23/04/2020 | 3 | 4 | 5 |
Values 1, 2 and 3 are parameters related to my industry.
I need a new column, named "analysis", that shall perform the following calculation:
analysis (today) = (value1(yesterday) + value2(yesterday)) - (value1(today) + value2(today)) + (value3(today) - value3(yesterday))
In words, the new column shall be calculated as the change in the sum of values 1 and 2, plus the addition occured on value 3.
In my example, the "analysis" result for "24/04/2020":
analysis_24/04/2020 = (3+4) - (4+1) + (6-5) = 7 - 5 + 1 = 3
Any insights? I've tried using FILTER combined with SELECTCOLUMNS, but considering a have a real table with far more than 2 rows, this is not working.
Thanks in advance!
Solved! Go to Solution.
Hi @JGRaiol ,
Try this calculated column:
Hi,
I added a row for 25 April 2020 with the following information 6,3,4. Here is the result i got. You may download my PBI file from here.
This measure should work @JGRaiol :
Analysis =
sumx(values(analysistable[Date]),
(calculate(sum(analysistable[v1]),PREVIOUSDAY(analysistable[Date]))
+calculate(sum(analysistable[v2]),PREVIOUSDAY(analysistable[Date])))
-
(sum(analysistable[v1])
+sum(analysistable[v2]))
+
(sum(analysistable[v3])
-calculate(sum(analysistable[v3]),PREVIOUSDAY(analysistable[Date]))))
Respectfully,
Zoe Douglas (DataZoe)
Follow me on LinkedIn at https://www.linkedin.com/in/zoedouglas-data
See my reports and blog at https://www.datazoepowerbi.com/
Hi @JGRaiol
it could be a column like
Column =
var _thisDate = Table[Date]
var _prevDate = CALCULATE(MAX(Table[Date]), Table[Date] < _thisDate)
var _prevVal1 = CALCULATE(MAX(Table[Value1]), Table[Date] = _prevDate)
var _prevVal2 = CALCULATE(MAX(Table[Value2]), Table[Date] = _prevDate)
var _prevVal3 = CALCULATE(MAX(Table[Value3]), Table[Date] = _prevDate)
RETURN
_prevVal1 + _prevVal2 - ([Value1] + [Value2]) + ([Value3] - _prevVal3 )
Hello @az38, thank you so much for your reply!
I perfectly understood your implementation, but when I put it on my BI, all prevdate and prevVals are returning blank, and not the proper value. I am implementing this as a calculated column.
Any insights?
Many thanks!
Hi @JGRaiol ,
Try this calculated column:
Thank you! That worked!
Best regards
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 |
---|---|
108 | |
100 | |
78 | |
64 | |
58 |
User | Count |
---|---|
148 | |
113 | |
97 | |
84 | |
67 |