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.
Solved! Go to Solution.
Hi prakash11440278 ,
You could try to create a index column by M code like below
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bdA9CoAwDAXgq5TMgk1if3BTZ08gDg4O4ub9BxWhKfjWfAlJ3rLQeJw7NRRbbjk7lt57N8xPhb2ntfk1FBfoNi/CwLW4Qu+KdyrAg3lC+2PxAO9L5oI8m+vn03bhfAR59T7gKp0A2MKRBNiy0fe39QY=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Vehicle = _t, #"Date&Time" = _t, Production = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Vehicle", type text}, {"Date&Time", type datetime}, {"Production", Int64.Type}}),
#"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1),
#"Grouped Rows" = Table.Group(#"Added Index", {"Vehicle"}, {{"a", each _, type table [Vehicle=text, #"Date&Time"=datetime, Production=number, Index=number]}}),
#"Added Custom" = Table.AddColumn(#"Grouped Rows", "index", each Table.AddIndexColumn([a], "in",1,1)),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"a"}),
#"Expanded index" = Table.ExpandTableColumn(#"Removed Columns", "index", {"Date&Time", "Production", "in"}, {"Date&Time", "Production", "in"})
in
#"Expanded index"
Then use below measure, you could refer to my sample
Measure =
SUM(T3[Production])
- CALCULATE (SUM(T3[Production])
,
FILTER ( ALLEXCEPT( T3,T3[Vehicle]), T3[in]=MIN(T3[in])-1 )
)
Best Regards,
Zoe Zhi
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hey,
you can solve your requirement using the static segmentation that is described in this article: https://www.daxpatterns.com/static-segmentation/
Create a calculated column in your table that "segments" the hours, and then use the new column.
Hopefully this is what you are looking for.
Regards,
Tom
Hey,
please provide a pbix/xlsx file that contains sample data, but still reflects your data model, meaning create sample data for all the tables necessary to re-create your "issue", upload the file(s) to onedrive or dropbox and share the link.
Regards,
Tom
Hi prakash11440278 ,
You could try to create a index column by M code like below
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bdA9CoAwDAXgq5TMgk1if3BTZ08gDg4O4ub9BxWhKfjWfAlJ3rLQeJw7NRRbbjk7lt57N8xPhb2ntfk1FBfoNi/CwLW4Qu+KdyrAg3lC+2PxAO9L5oI8m+vn03bhfAR59T7gKp0A2MKRBNiy0fe39QY=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Vehicle = _t, #"Date&Time" = _t, Production = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Vehicle", type text}, {"Date&Time", type datetime}, {"Production", Int64.Type}}),
#"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1),
#"Grouped Rows" = Table.Group(#"Added Index", {"Vehicle"}, {{"a", each _, type table [Vehicle=text, #"Date&Time"=datetime, Production=number, Index=number]}}),
#"Added Custom" = Table.AddColumn(#"Grouped Rows", "index", each Table.AddIndexColumn([a], "in",1,1)),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"a"}),
#"Expanded index" = Table.ExpandTableColumn(#"Removed Columns", "index", {"Date&Time", "Production", "in"}, {"Date&Time", "Production", "in"})
in
#"Expanded index"
Then use below measure, you could refer to my sample
Measure =
SUM(T3[Production])
- CALCULATE (SUM(T3[Production])
,
FILTER ( ALLEXCEPT( T3,T3[Vehicle]), T3[in]=MIN(T3[in])-1 )
)
Best Regards,
Zoe Zhi
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
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 |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
93 | |
84 | |
66 |