Detección de carencias

[redhughes]

Contamos con una base de datos de formularios de comunicación de nuestros clientes que contiene las fechas de inicio y finalización de los formularios:

Cliente	Empezar	Final
John Smith	01/06/2020	30/06/2020
Jane Smith	01/06/2020	30/06/2020
John Smith	01/07/2020	31/07/2020
Jane Smith	10/07/2020	31/07/2020

Estamos buscando una manera de informar sobre las lagunas en la documentación, es decir, un informe que de alguna manera señalaría que Jane Smith no ha tenido un formulario de comunicación que cubra el período del 01/07/2020-09/07/2020. ¿Alguien tiene alguna idea brillante, por favor? 🙂

[tex628 · Accepted solution]

Esta columna devuelve el espacio entre la fila actual y la comunicación más reciente del mismo cliente.

Column = 
VAR Name_ = Query1[Client]
VAR End_ = Query1[End]
VAR Start_ = Query1[Start]
VAR Gap = Start_ - CALCULATE(MAX(Query1[End]) , ALL(Query1) , Query1[End] < Start_ , Query1[Client] = Name_) -1
Return 
Gap

Br
J

[v-deddai1-msft · Accepted solution]

Hola @redhughes ,

Sólo tiene que utilizar la medida a continuación:

Measure =
VAR start_ =
    MAX ( 'Table'[Start] )
VAR lastend_ =
    MAXX (
        FILTER (
            ALL ( 'Table' ),
            'Table'[Client] = MAX ( 'Table'[Client] )
                && 'Table'[End] <= start_
        ),
        'Table'[End]
    )
RETURN
    IF (
        DATEDIFF ( lastend_, start_, DAY ) > 1,
        lastend_ + 1 & "-" & start_ - 1 & "gap",
        "No Gap"
    )

Si este post ayuda, entonces considera Aceptarlo como la solución para ayudar a los otros miembros a encontrarlo más rápidamente.

Saludos

Dedmon Dai

[tex628 · Accepted solution]

Esta columna devuelve el espacio entre la fila actual y la comunicación más reciente del mismo cliente.

Column = 
VAR Name_ = Query1[Client]
VAR End_ = Query1[End]
VAR Start_ = Query1[Start]
VAR Gap = Start_ - CALCULATE(MAX(Query1[End]) , ALL(Query1) , Query1[End] < Start_ , Query1[Client] = Name_) -1
Return 
Gap

Br
J

[Greg_Deckler]

@redhughes - Debería poder llegar allí usando Entradas Abiertas: https://community.powerbi.com/t5/Quick-Measures-Gallery/Open-Tickets/m-p/409364#M147

[Fowmy]

@redhughes

En Power Query, pegue el código debajo de la consulta en blanco en el editor avanzado:

Puede descargar el archivo: HERE

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8srPyFMIzs0syVDSUTIw1Dcw0zcyMDIAcowN4JxYHaDCxLxU4hSim2gOV4jgYJhoaIBDYSwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Client = _t, Start = _t, End = _t]),
    #"Changed Type with Locale" = Table.TransformColumnTypes(Source, {{"Start", type date}, {"End", type date}}, "en-GB"),
    Step1 = Table.TransformColumns(#"Changed Type with Locale",{{"Client", Text.Trim, type text}}),
    #"Grouped Rows" = Table.Group(Step1, {"Client"}, {{"_Min", each List.Min([Start]), type nullable date}, {"_Max", each List.Max([End]), type nullable date}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each { Number.From ([_Min]).. Number.From ([_Max])}),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded Custom",{{"Custom", type date}}),
    Due = Table.RemoveColumns(#"Changed Type",{"_Min", "_Max"}),
    Custom1 = Step1,
    #"Added Custom1" = Table.AddColumn(Custom1, "Custom", each { Number.From ([Start]).. Number.From ([End])}),
    #"Expanded Custom1" = Table.ExpandListColumn(#"Added Custom1", "Custom"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Custom1",{{"Custom", type date}}),
    Actual = Table.RemoveColumns(#"Changed Type1",{"Start", "End"}),
    #"Merged Queries" = Table.NestedJoin(Due, {"Client", "Custom"}, Actual, {"Client", "Custom"}, "Actual", JoinKind.LeftOuter),
    #"Expanded Actual" = Table.ExpandTableColumn(#"Merged Queries", "Actual", {"Custom"}, {"Custom.1"}),
    #"Filtered Rows" = Table.SelectRows(#"Expanded Actual", each ([Custom.1] = null)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Custom.1"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Custom", "Missing Dates"}})
in
    #"Renamed Columns"

________________________

¿He respondido a tu pregunta? Marque este post como una solución, esto ayudará a otros!.

Haga clic en el icono Thumbs-Up a la derecha si le gusta esta respuesta 🙂

YoutubeLinkedin

[Anonymous]

Puede agregar una columna adicional usando Datediff

Column = DATEDIFF('Table (2)'[Start],'Table (2)'[End],DAY)

A continuación, utilice la opción de formato condicional en los colores de columna.

[amitchandak]

@redhughes , Compruebe si esto puede ayudar a obtener el GAP

if(datediff(maxx(filter(Table, [Client]-earlier([Client]) && [Start]<earlier([Start])),[End]),[Start],day) >1, "Gap","No Gap")