Calculate Table

[Anonymous]

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

in this:

Ticket	Period	Days by Month
abc	01/jan	10
def	01/jan	31
ghi	01/jan	31
jkl	01/jan	31
def	01/fev	1
ghi	01/fev	10
jkl	01/fev	29
jkl	01/mar	10

The question that I need to answer is: How long has a ticket been open each month?

Tks guys!!

[Anonymous · Accepted solution]

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]

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"

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)

[Ashish_Mathur]

Hi,

Download my PBI file from here.

Hope this helps.

[Greg_Deckler]

@Anonymous - Check out Open Tickets. It will be the same basic concept. https://community.powerbi.com/t5/Quick-Measures-Gallery/Open-Tickets/m-p/409364#M147

[Anonymous]

I appreciate the answer but it doesn’t answer my question.  How long has a ticket been open each month?

[Ashish_Mathur]

Hi,

Have you checked my result?