Forum Discussion
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 |
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!!
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.
6 Replies
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)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
Hi,
Have you checked my result?
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.
