Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!!
Solved! Go to 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.
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.
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"
Hi,
Download my PBI file from here.
Hope this helps.
@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
I appreciate the answer but it doesn’t answer my question. How long has a ticket been open each month?
Hi,
Have you checked my result?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
93 | |
77 | |
65 | |
53 |
User | Count |
---|---|
147 | |
106 | |
104 | |
87 | |
61 |