Change date history table

[Anonymous]

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

[v-yiruan-msft · Accepted 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

[Greg_Deckler]

@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

[Anonymous]

Thank You Greg

In the script i see [Risk ID] that I don't recognize?

Cheers,

Martin

[amitchandak]

@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])

[Anonymous]

Thank You Amitchandak for taking your time.

When adding Boardlocation, I get same value as the original ChangeDate?