Forum Discussion
Request DAX For Ranked Order Date Tracking for Different Orders on Same Date
- 2 years ago
Hi,
thank you for your message, and pleaes check the below pictures and the attached pbix file.
Hi etane ,
Thanks to Jihwan_Kim for the solution. Here is my solution
Here some steps that I want to share, you can check them if they suitable for your requirement.
Here is my test data:
Copy the following code into the advanced editor of Power Query
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wci4tLsnPTS1SMFTSUQooyk8pTS5RcASyXVJzMstSi1JTgGxDfUN9IwMjY6VYHaJ1GJGow4hkO4zIsMMYiw4jvP4wIVmHOc11AMPKlC5azEjWYmQA1RILAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Sales Table" = _t, Product = _t, Status = _t, #"Order Date" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Sales Table", type text}, {"Product", type text}, {"Status", type text}, {"Order Date", type date}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Sales Table", "Customer"}}),
#"Grouped Rows" = Table.Group(#"Renamed Columns", {"Customer"}, {{"Count", each _, type table [Customer=nullable text, Product=nullable text, Status=nullable text, Order Date=nullable date]}}),
#"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([Count],"index",1,1)),
#"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Product", "Status", "Order Date", "index"}, {"Custom.Product", "Custom.Status", "Custom.Order Date", "Custom.index"}),
#"Removed Columns" = Table.RemoveColumns(#"Expanded Custom",{"Count", "Custom.Product"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Removed Columns",{{"Custom.Order Date", type date}})
in
#"Changed Type1"
Create a measure
Measure =
CALCULATE(
DATEDIFF(MIN('Sales Table'[Custom.Order Date]),MAX('Sales Table'[Custom.Order Date]),DAY),
ALLEXCEPT(
'Sales Table',
'Sales Table'[Custom.index]
)
)
Final output
Best regards,
Albert He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
- etane2 years ago
Helper V
Anonymous Thank you for your assistance. I have a couple of questions.
1) I've been trying to find a rank formula that resets to 1 by customer and you've seen to done so in your base table. How did you do that?
2) I am trying to capture first, second ... orders perhaps by rank after it resets by customer. Then, calculate the days difference between each order. I think you're trying to do that but I am not sure what your "measure" results actually reflect?