Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Calculate Table

Hi guys.   I'm trying to transform the following table: Ticket Open Close Age abc 01/jan 10/jan 10 def 01/jan 01/fev 32 ghi 01/jan 10/fev 41 jkl 01/jan 10/mar 70 ...
Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi Anonymous ,

     

    1.My sample data is this.

    Ticket

    Age

    Open

    Close

    abc

    10

    01/Jan

    10/Jan

    def

    32

    01/Jan

    01/Feb

    ghi

    41

    01/Jan

    10/Feb

    jkl

    70

    01/Jan

    10/Mar

     

    2.Create calculated columns to get Open date and Close date.

    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.Create a calendar table.

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

     

    4.Create a new table which is a combination and filtering of the previous two tables.

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

     

     

    You can check more details from here.

     

     

     

    Best Regards,

    Stephen Tao

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

FrankAT's avatar
FrankAT
Community Champion
5 years ago

Hello @Williamspsouza

you can do this with Power Query as follows:

// 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"

03-09-_2020_01-32-18.png

No Bothers With The Date Format. That It In The Way From I Region.
With kind greetings from the city where the legend of the 'Pied Piper de Hamelin' is at home
FrankAT (Proud to be a Datanaut)