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,
I would like ask how to properly write DAX formula. Because calculating the difference between the values I am getting wrong answers. For example, sample data:
My result after calculation:
But I have expected this result:
So result differs from 0.87. (-3289.13!=0.87)
So seems something wrong with my query.
Here is my formula:
VAR _0 = MAXX(FILTER('x','x'[date]<EARLIER('x'[date]) && 'x'[ID]= EARLIER('x'[ID])),[date])
VAR _1 = MAXX(FILTER('x','x'[date] =_1 && 'x'[ID]= EARLIER('x'[ID]) ),[cre])
return
if('x'[deb] <> 0,_1 - 'x'[deb], blank())
Solved! Go to Solution.
Hi @Analitika ,
I would recommend you first transform your data in power query using below M codes:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlLSUSrKLzMEUmb6xgb6RgZGILaxkaUBkAJhYwOlWB00hYaWMIVgFUYWlnqGxkCWKVBtLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, OBJ_ID = _t, DATE = _t, CRE = _t, DET = _t, OPE_ID = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"OBJ_ID", type text}, {"DATE", type date}, {"CRE", Int64.Type}, {"DET", type number}, {"OPE_ID", Int64.Type}}),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"ID", "OBJ_ID", "DATE"}, "Attribute", "Value"),
#"Grouped Rows" = Table.Group(#"Unpivoted Columns", {"ID", "DATE"}, {{"OBJ_ID", each List.Max([OBJ_ID]), type nullable text}, {"Value", each List.Max([Value]), type number}})
in
#"Grouped Rows"
And you will see:
Then create a calculated column as below:
Difference =
var _mindate=CALCULATE(MIN('Table'[DATE]),FILTER('Table','Table'[ID]=EARLIER('Table'[ID])&&'Table'[DATE]>EARLIER('Table'[DATE])))
var _maxvalue=CALCULATE(MAX('Table'[Value]),FILTER('Table','Table'[DATE]=_mindate))
Return
IF(_maxvalue=BLANK(),BLANK(),_maxvalue-'Table'[Value])
And you will see:
For the related .pbix file,pls see attached.
Best Regards,
Kelly
Did I answer your question? Mark my reply as a solution!
Hi @Analitika ,
I would recommend you first transform your data in power query using below M codes:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlLSUSrKLzMEUmb6xgb6RgZGILaxkaUBkAJhYwOlWB00hYaWMIVgFUYWlnqGxkCWKVBtLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, OBJ_ID = _t, DATE = _t, CRE = _t, DET = _t, OPE_ID = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"OBJ_ID", type text}, {"DATE", type date}, {"CRE", Int64.Type}, {"DET", type number}, {"OPE_ID", Int64.Type}}),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"ID", "OBJ_ID", "DATE"}, "Attribute", "Value"),
#"Grouped Rows" = Table.Group(#"Unpivoted Columns", {"ID", "DATE"}, {{"OBJ_ID", each List.Max([OBJ_ID]), type nullable text}, {"Value", each List.Max([Value]), type number}})
in
#"Grouped Rows"
And you will see:
Then create a calculated column as below:
Difference =
var _mindate=CALCULATE(MIN('Table'[DATE]),FILTER('Table','Table'[ID]=EARLIER('Table'[ID])&&'Table'[DATE]>EARLIER('Table'[DATE])))
var _maxvalue=CALCULATE(MAX('Table'[Value]),FILTER('Table','Table'[DATE]=_mindate))
Return
IF(_maxvalue=BLANK(),BLANK(),_maxvalue-'Table'[Value])
And you will see:
For the related .pbix file,pls see attached.
Best Regards,
Kelly
Did I answer your question? Mark my reply as a solution!
@Analitika , Please find the attached file
One of the two
Calc 1 =
var _1 = maxx(FILTER('Table', [ID] =EARLIER('Table'[ID]) && [CRE]<>0) ,[CRE])
return if([DET] <>0, _1 -[DET])
Calc 12 =
var _1 = maxx(FILTER('Table', [ID] =EARLIER('Table'[ID]) && [CRE]<>0 && [DATE] >EARLIER('Table'[DATE]) ) ,[CRE])
return if([DET] <>0, _1 -[DET])
Hello, is possible to make filter that ID will be like 5000 because same object has value with id 5000
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 |
---|---|
97 | |
96 | |
82 | |
74 | |
66 |
User | Count |
---|---|
121 | |
105 | |
102 | |
82 | |
72 |