Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Change date history table

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...
Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    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's avatar
Greg_Deckler
Community Champion
5 years ago

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's avatar
Anonymous
Not applicable
5 years ago

Thank You Greg

 

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

 

Cheers,

Martin