Dax formula help

[prakash11440278]

Hi All,

Can you please provide the dax formula for the below.
Input data:
Vehicle Date&Time Production
Bike 6/1/18 12:00 AM 100
Bike 6/1/18 1:00 AM 200
Bike 6/1/18 2:00 AM 221
Bike 6/1/18 3:00 AM 321
Bike 6/1/18 4:00 AM 432
Bike 6/1/18 5:00 AM 470
Bike 6/1/18 6:00 AM 500
Bike 6/1/18 7:00 AM 520
Bike 6/1/18 8:00 AM 530
Car 6/1/18 12:00 AM 120
Car 6/1/18 1:00 AM 220
Car 6/1/18 2:00 AM 250
Car 6/1/18 3:00 AM 270
Car 6/1/18 4:00 AM 300

Expected Result:
Vehicle Segment Production
Bike 12-1 AM 100
Bike 1-2 AM 21
Bike 2-3 AM 100
Bike 3-4 AM 111
Bike 4-5 AM 38
Bike 5-6 AM 30
Bike 6-7 AM 20
Bike 7-8 AM 10
Car 12-1 AM 100
Car 1-2 AM 30
Car 2-3 AM 20
Car 3-4 AM 30

Thanks in advance

[TomMartens]

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

[prakash11440278]

Hi Tom,

Looks like the numbers are cumulative sum. How can we calculate difference between current hour and previous hour for each vehicle type.

Thanks

[TomMartens]

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

[prakash11440278]

Please find the below link.
https://1drv.ms/x/s!Ak2M-PyNMwZ1ZxWHkCzm42QuVmI?e=nS4sxy

[dax · Accepted 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.