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 all,
Anyone, who can see what I do wrong here?
Using this script and get the Column PreviousWrong, but I anned it to be as 'PreviousWant'
PreviousWrong = coalesce(maxx(filter(Table,Table[WorkItemId] = earlier(Table[WorkItemId]) && Table[ChangedDate] < earlier(Table[ChangedDate]) ),Table[ChangedDate]),Table[ChangedDate])
WorkItemId | BoardLocationSK | ChangedDate | PreviousWrong | PreviousWant:-) |
1 | 50887034 | 14/08/2020 | 14/08/2020 | 14/08/2020 |
1 | 50887034 | 14/08/2020 | 14/08/2020 | 14/08/2020 |
1 | 50905818 | 20/08/2020 | 14/08/2020 | 14/08/2020 |
1 | 50905818 | 20/08/2020 | 14/08/2020 | 20/08/2020 |
1 | 50905818 | 20/08/2020 | 14/08/2020 | 20/08/2020 |
1 | 50905818 | 20/08/2020 | 14/08/2020 | 20/08/2020 |
3 | 50887496 | 14/08/2020 | 14/08/2020 | 14/08/2020 |
3 | 50887496 | 14/08/2020 | 14/08/2020 | 14/08/2020 |
3 | 50905907 | 20/08/2020 | 14/08/2020 | 14/08/2020 |
3 | 50905911 | 21/08/2020 | 20/08/2020 | 20/08/2020 |
3 | 50905911 | 21/08/2020 | 20/08/2020 | 21/08/2020 |
3 | 50905911 | 21/08/2020 | 20/08/2020 | 21/08/2020 |
3 | 50905911 | 21/08/2020 | 20/08/2020 | 21/08/2020 |
3 | 50905911 | 27/08/2020 | 21/08/2020 | 21/08/2020 |
3 | 50905911 | 27/08/2020 | 21/08/2020 | 27/08/2020 |
3 | 50905907 | 31/08/2020 | 27/08/2020 | 27/08/2020 |
3 | 50905911 | 01/09/2020 | 31/08/2020 | 31/08/2020 |
3 | 50905911 | 01/09/2020 | 31/08/2020 | 01/09/2020 |
3 | 50905907 | 08/09/2020 | 01/09/2020 | 01/09/2020 |
3 | 50905907 | 08/09/2020 | 01/09/2020 | 08/09/2020 |
3 | 50905907 | 08/09/2020 | 01/09/2020 | 08/09/2020 |
4 | 50905908 | 20/08/2020 | 20/08/2020 | 20/08/2020 |
4 | 50905911 | 21/08/2020 | 20/08/2020 | 20/08/2020 |
4 | 50905911 | 21/08/2020 | 20/08/2020 | 21/08/2020 |
Solved! Go to Solution.
Hi @Anonymous ,
First, please add a index column in Power Query Editor:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("xdFBCsAgDATAv3gW3GisyVuk//9G9VAUxKKU0ltgYNkkORsy1kSIJAQuo4eHE0dsTruEiigkDT2+xHAXYj2GQgtYYhVpEluRqEP6AdMO9quEeSzUYU9bcFV5rdx0fCo/nWmO5wU=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [WorkItemId = _t, BoardLocationSK = _t, ChangedDate = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"WorkItemId", Int64.Type}, {"BoardLocationSK", Int64.Type}, {"ChangedDate", type text}}),
#"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1, Int64.Type)
in
#"Added Index"
Then create a measure as below:
Measure =
VAR _index =
MAX ( 'Table'[Index] )
VAR _cdate =
CALCULATE (
MAX ( 'Table'[ChangedDate] ),
FILTER (
ALLSELECTED ( 'Table' ),
'Table'[WorkItemId] = MAX ( 'Table'[WorkItemId] )
&& 'Table'[Index] < _index
)
)
RETURN
IF ( ISBLANK ( _cdate ), MAX ( 'Table'[ChangedDate] ), _cdate )
Or you can update the formula of calculated column "PreviousWrong" as below after the index column be added:
PreviousWrong =
COALESCE (
MAXX (
FILTER (
'Table',
'Table'[WorkItemId] = EARLIER ( 'Table'[WorkItemId] )
&& 'Table'[Index] < EARLIER ( 'Table'[Index] )
),
'Table'[ChangedDate]
),
'Table'[ChangedDate]
)
Best Regards
Rena
Hi @Anonymous ,
First, please add a index column in Power Query Editor:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("xdFBCsAgDATAv3gW3GisyVuk//9G9VAUxKKU0ltgYNkkORsy1kSIJAQuo4eHE0dsTruEiigkDT2+xHAXYj2GQgtYYhVpEluRqEP6AdMO9quEeSzUYU9bcFV5rdx0fCo/nWmO5wU=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [WorkItemId = _t, BoardLocationSK = _t, ChangedDate = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"WorkItemId", Int64.Type}, {"BoardLocationSK", Int64.Type}, {"ChangedDate", type text}}),
#"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1, Int64.Type)
in
#"Added Index"
Then create a measure as below:
Measure =
VAR _index =
MAX ( 'Table'[Index] )
VAR _cdate =
CALCULATE (
MAX ( 'Table'[ChangedDate] ),
FILTER (
ALLSELECTED ( 'Table' ),
'Table'[WorkItemId] = MAX ( 'Table'[WorkItemId] )
&& 'Table'[Index] < _index
)
)
RETURN
IF ( ISBLANK ( _cdate ), MAX ( 'Table'[ChangedDate] ), _cdate )
Or you can update the formula of calculated column "PreviousWrong" as below after the index column be added:
PreviousWrong =
COALESCE (
MAXX (
FILTER (
'Table',
'Table'[WorkItemId] = EARLIER ( 'Table'[WorkItemId] )
&& 'Table'[Index] < EARLIER ( 'Table'[Index] )
),
'Table'[ChangedDate]
),
'Table'[ChangedDate]
)
Best Regards
Rena
@Anonymous Assuming those are actual Date columns, this should be:
Previous =
VAR __PreviousDate = MAXX(FILTER('Table (25)',[WorkItemID]=EARLIER([WorkItemID]) && [BoardLocation]=EARLIER([BoardLocation])&& [ChangedDate]<EARLIER([ChangedDate])),[ChangedDate])
VAR __Count = COUNTX(FILTER('Table (25)',[WorkItemID]=EARLIER([WorkItemID]) && [BoardLocation]<EARLIER([BoardLocation]) && [ChangedDate]<EARLIER([ChangedDate])),[Risk ID])
RETURN
IF(__Count<0 || ISBLANK(__PreviousDate),[ChangedDate],__PreviousDate)
So, if that doesn't work I suspect that those are text and not date columns, just a hunch.
See my article on Mean Time Between Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/339586.
The basic pattern is:
Column =
VAR __Current = [Value]
VAR __PreviousDate = MAXX(FILTER('Table','Table'[Date] < EARLIER('Table'[Date])),[Date])
VAR __Previous = MAXX(FILTER('Table',[Date]=__PreviousDate),[Value])
RETURN
__Current - __Previous
Thank You Greg
In the script i see [Risk ID] that I don't recognize?
Cheers,
Martin
@Anonymous , I think you need to add BoardLocationSK in filter
PreviousWrong = coalesce(maxx(filter(Table,Table[WorkItemId] = earlier(Table[WorkItemId]) && [BoardLocationSK] = earlier(Table[BoardLocationSK])
Table[ChangedDate] < earlier(Table[ChangedDate]) ),Table[ChangedDate]),Table[ChangedDate])
Thank You Amitchandak for taking your time.
When adding Boardlocation, I get same value as the original ChangeDate?
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 | |
104 | |
79 | |
68 | |
61 |
User | Count |
---|---|
143 | |
104 | |
103 | |
82 | |
70 |