Calcular tabla

Hola @Williamspsouza

puede hacerlo con Power Query de la siguiente manera:

// Table
let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSkxKVtJRMjDUByIjAyMDIMfQAIWjFKsTrZSSmoauDMQxgnGMjcDK0jMysZkGV2ZiCFaWlZ2DTZkxjGMOtDQWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Ticket = _t, Open = _t, Close = _t, Age = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Ticket", type text}, {"Open", type date}, {"Close", type date}, {"Age", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each {Number.From([Open])..Number.From([Close])}),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Custom",{{"Custom", type date}}),
    #"Inserted Month" = Table.AddColumn(#"Changed Type1", "Month", each Date.Month([Custom]), Int64.Type),
    #"Added Custom1" = Table.AddColumn(#"Inserted Month", "Day", each 1),
    #"Changed Type2" = Table.TransformColumnTypes(#"Added Custom1",{{"Day", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type2", {"Ticket", "Month"}, {{"Sum", each List.Sum([Day]), type nullable number}}),
    #"Added Custom2" = Table.AddColumn(#"Grouped Rows", "Period", each #date(2020,[Month],1)),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom2",{"Month"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"Ticket", "Period", "Sum"}),
    #"Changed Type3" = Table.TransformColumnTypes(#"Reordered Columns",{{"Period", type date}})
in
    #"Changed Type3"

Hola @Williamspsouza ,

1.My datos de ejemplo es este.

2.Crear columnas calculadas para obtener la fecha de apertura y la fecha de cierre.

OpenDate = 
VAR month =
    SWITCH (
        RIGHT ( 'Table'[Open], 3 ),
        "Jul", 7,
        "Aug", 8,
        "Sep", 9,
        "Oct", 10,
        "Nov", 11,
        "Dec", 12,
        "Jan", 1,
        "Feb", 2,
        "Mar", 3,
        "Apr", 4,
        "May", 5,
        "Jun", 6
    )
RETURN
    DATE ( 2020, month, LEFT ( 'Table'[Open], 2 ) )

CloseDate = 
VAR month =
    SWITCH (
        RIGHT ( 'Table'[Close], 3 ),
        "Jul", 7,
        "Aug", 8,
        "Sep", 9,
        "Oct", 10,
        "Nov", 11,
        "Dec", 12,
        "Jan", 1,
        "Feb", 2,
        "Mar", 3,
        "Apr", 4,
        "May", 5,
        "Jun", 6
    )
RETURN
    DATE ( 2020, month, LEFT ( 'Table'[Close], 2 ) )

3.Cree una tabla de calendario.

Dates =
ADDCOLUMNS (
    CALENDAR ( DATE ( 2020, 1, 1 ), DATE ( 2020, 12, 31 ) ),
    "day", DAY ( [Date] ),
    "Period",
        DAY ( [Date] ) & "/"
            & FORMAT ( [Date], "mmm" )
)

4.Crear una nueva tabla que es una combinación y filtrado de las dos tablas anteriores.

NewTable =
SUMMARIZE (
    ADDCOLUMNS (
        FILTER (
            CROSSJOIN ( 'Dates', 'Table' ),
            [Date] <= [CloseDate]
                && [Date] >= [OpenDate]
                && [day] = 1
        ),
        "Days by Month",
            IF (
                EOMONTH ( [Date], 0 ) < [CloseDate],
                DAY ( EOMONTH ( [Date], 0 ) ),
                DAY ( [CloseDate] )
            )
    ),
    [Ticket],
    [Period],
    [Days by Month]
)

Puede consultar más detalles desde aquí.

Saludos

Stephen Tao

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