Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I have a table of sales increasingly with the datetime.
For Example:
Date Time Total Sales
2019-11-07 01:00:00 50000
2019-11-07 03:00:00 55000
2019-11-08 07:00:00 59000
2019-11-08 14:00:00 40000
2019-11-09 06:00:00 58000
2019-11-09 10:00:00 70000
I want to build a table to summarize like following:
Date Time Total Sales
2019-11-07 03:00:00 55000
2019-11-08 14:00:00 40000
2019-11-09 10:00:00 70000
Can anyone help??
Solved! Go to Solution.
Hi @Jamie_wong !
I suggest to create a Date Time table fisrt, for example in PowerQuery, to capture all date and time values.
Connect it to your original Sales table, to the Date Time values.
Try to determine the last hour of every day with DAX:
Hi Jamie_wong,
You could use below M code to create rank column
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dc3BDcAgDAPAVaK8i+QUaEhXidh/jSL6IWqx/DzZ7sz9cD4hlkQSlCA3MEozFSOTfFwOrv67RtDgbOekrK5sfo1whb22c4LV6bvXHw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Date Time Total Sales" = _t]),
#"Split Column by Delimiter" = Table.SplitColumn(Source, "Date Time Total Sales", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.None, true), {"Date Time Total Sales.1", "Date Time Total Sales.2"}),
#"Removed Blank Rows" = Table.SelectRows(#"Split Column by Delimiter", each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null}))),
#"Changed Type" = Table.TransformColumnTypes(#"Removed Blank Rows",{{"Date Time Total Sales.1", type datetime}, {"Date Time Total Sales.2", Int64.Type}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Date Time Total Sales.1", "Date Time"}, {"Date Time Total Sales.2", "Total Sales"}}),
#"Duplicated Column" = Table.DuplicateColumn(#"Renamed Columns", "Date Time", "Date Time - Copy"),
#"Changed Type1" = Table.TransformColumnTypes(#"Duplicated Column",{{"Date Time - Copy", type date}}),
#"Added Index" = Table.AddIndexColumn(#"Changed Type1", "Index", 0, 1),
#"Grouped Rows" = Table.Group(#"Added Index", {"Date Time - Copy"}, {{"all", each _, type table [Date Time=datetime, Total Sales=number, #"Date Time - Copy"=date, Index=number]}}),
#"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([all], "rank",1,1)),
#"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Date Time", "Total Sales", "rank"}, {"Custom.Date Time", "Custom.Total Sales", "Custom.rank"}),
#"Removed Columns" = Table.RemoveColumns(#"Expanded Custom",{"Date Time - Copy", "all"})
in
#"Removed Columns"
Then use filter to achieve this goal
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.
Hi @Jamie_wong !
I suggest to create a Date Time table fisrt, for example in PowerQuery, to capture all date and time values.
Connect it to your original Sales table, to the Date Time values.
Try to determine the last hour of every day with DAX:
User | Count |
---|---|
127 | |
108 | |
99 | |
63 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |