Cambiar tabla de historial de fechas

[Anonymous]

Hola a todos

¿Alguien puede ver lo que hago mal aquí?

Usando este script y obtener la Columna PreviousWrong, pero lo recocido para ser como 'PreviousWant'

PreviousWrong á coalesce(maxx(filter(Table,Table[WorkItemId] á earlier(Table[WorkItemId]) && Table[ChangedDate] < earlier(Table[ChangedDate]) ),Table[ChangedDate]),Table[ChangedDate])

WorkItemId	BoardLocationSK	ChangedDate	PreviousWrong	AnteriorWant:-)
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]

Hola @MartinLomborg ,

En primer lugar, agregue una columna de índice en 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"

A continuación, cree una medida como se indica a continuación:

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 )​

O puede actualizar la fórmula de la columna calculada "PreviousWrong" como se agrega a continuación después de agregar la columna de índice:

PreviousWrong = 
COALESCE (
    MAXX (
        FILTER (
            'Table',
            'Table'[WorkItemId] = EARLIER ( 'Table'[WorkItemId] )
                && 'Table'[Index] < EARLIER ( 'Table'[Index] )
        ),
        'Table'[ChangedDate]
    ),
    'Table'[ChangedDate]
)

Saludos

Rena

[Greg_Deckler]

@MartinLomborg Suponiendo que son columnas Date reales, esto debe ser:

Previous = 
    VAR __PreviousDate = MAXX(FILTER('Table (25)',[WorkItemID]=EARLIER([WorkItemID]) && [BoardLocation]<EARLIER([BoardLocation])),[ChangedDate])
    VAR __Count = COUNTX(FILTER('Table (25)',[WorkItemID]=EARLIER([WorkItemID]) && [BoardLocation]<EARLIER([BoardLocation])),[Risk ID])
RETURN
    IF(__Count<0 || ISBLANK(__PreviousDate),[ChangedDate],__PreviousDate)

Por lo tanto, si eso no funciona sospecho que son columnas de texto y no fecha, sólo una corazonada.

Vea mi artículo sobre el tiempo medio entre fallas (MTBF) que utiliza ANTES: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/3395....
El patrón básico es:
Columna ?
VAR __Current á [Valor]
VAR __PreviousDate á MAXX(FILTER('Table','Table'[Date] < EARLIER('Table'[Date])),[Date])

VAR __Previous á MAXX(FILTER('Table',[Fecha]-__PreviousDate),[Valor])
devolución
__Current - __Previous

[Anonymous]

Gracias Greg

En el script veo [ID de riesgo] que no reconozco?

Salud

Martin

[amitchandak]

@MartinLomborg , creo que necesita agregar BoardLocationSK en el filtro

PreviousWrong á coalesce(maxx(filter(Table,Table[WorkItemId] á earlier(Table[WorkItemId]) && [BoardLocationSK] á earlier(Table[BoardLocationSK])
Tabla[ChangedDate] < earlier(Table[ChangedDate]) ),Table[ChangedDate]),Table[ChangedDate])

[Anonymous]

Gracias Amitchandak por tomarse su tiempo.

Al agregar Boardlocation, obtengo el mismo valor que el ChangeDate original?