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
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?