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.
Hi,
I have the below query to create a new table showing dates and times for the past 24hrs.
How do i modify this to show the last 24hrs from the current (NOW) time?
Thanks
Jeff
let
Source = List.Dates(DateTime.Date(DateTime.LocalNow()), 1, #duration(-1, 0, 0, 0)),
#"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Column1", type date}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "TimeColumn", each List.Times(#time(Time.Hour(DateTime.LocalNow()),0,0), 24, #duration(0, 1, 0, 0))),
#"Expanded TimeColumn1" = Table.ExpandListColumn(#"Added Custom", "TimeColumn"),
#"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Expanded TimeColumn1", {{"Column1", type text}, {"TimeColumn", type text}}, "en-AU"),{"Column1", "TimeColumn"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"DateTime"),
#"Added Custom2" = Table.AddColumn(#"Merged Columns", "Custom.1", each DateTime.From(DateTime.From([DateTime]))),
#"Sorted Rows" = Table.Sort(#"Added Custom2",{{"Custom.1", Order.Ascending}})
in
#"Sorted Rows"
Solved! Go to Solution.
Hi @uthall ,
We can achieve that by DAX.
DateTime = var k = ADDCOLUMNS ( CROSSJOIN ( CALENDAR ( TODAY()-1, TODAY() ), UNION ( ROW ( "Time", TIME ( 1, 0, 0 ) ), ROW ( "Time", TIME ( 2, 0, 0 ) ), ROW ( "Time", TIME ( 3, 0, 0 ) ), ROW ( "Time", TIME ( 4, 0, 0 ) ), ROW ( "Time", TIME ( 5, 0, 0 ) ), ROW ( "Time", TIME ( 6, 0, 0 ) ), ROW ( "Time", TIME ( 7, 0, 0 ) ), ROW ( "Time", TIME ( 9, 0, 0 ) ), ROW ( "Time", TIME ( 10, 0, 0 ) ), ROW ( "Time", TIME ( 11, 0, 0 ) ), ROW ( "Time", TIME ( 12, 0, 0 ) ), ROW ( "Time", TIME ( 13, 0, 0 ) ), ROW ( "Time", TIME ( 14, 0, 0 ) ), ROW ( "Time", TIME ( 15, 0, 0 ) ), ROW ( "Time", TIME ( 16, 0, 0 ) ), ROW ( "Time", TIME ( 17, 0, 0 ) ), ROW ( "Time", TIME ( 18, 0, 0 ) ), ROW ( "Time", TIME ( 19, 0, 0 ) ), ROW ( "Time", TIME ( 20, 0, 0 ) ), ROW ( "Time", TIME ( 21, 0, 0 ) ), ROW ( "Time", TIME ( 22, 0, 0 ) ), ROW ( "Time", TIME ( 23, 0, 0 ) ), ROW ( "Time", TIME ( 24, 0, 0 ) ) ) ), "DateTime", [Date] + [Time], "Hour", HOUR ( [Time] ) ) return FILTER(k,NOW()-1 <=[DateTime] && [DateTime]<=NOW())
Hi @uthall ,
We can achieve that by DAX.
DateTime = var k = ADDCOLUMNS ( CROSSJOIN ( CALENDAR ( TODAY()-1, TODAY() ), UNION ( ROW ( "Time", TIME ( 1, 0, 0 ) ), ROW ( "Time", TIME ( 2, 0, 0 ) ), ROW ( "Time", TIME ( 3, 0, 0 ) ), ROW ( "Time", TIME ( 4, 0, 0 ) ), ROW ( "Time", TIME ( 5, 0, 0 ) ), ROW ( "Time", TIME ( 6, 0, 0 ) ), ROW ( "Time", TIME ( 7, 0, 0 ) ), ROW ( "Time", TIME ( 9, 0, 0 ) ), ROW ( "Time", TIME ( 10, 0, 0 ) ), ROW ( "Time", TIME ( 11, 0, 0 ) ), ROW ( "Time", TIME ( 12, 0, 0 ) ), ROW ( "Time", TIME ( 13, 0, 0 ) ), ROW ( "Time", TIME ( 14, 0, 0 ) ), ROW ( "Time", TIME ( 15, 0, 0 ) ), ROW ( "Time", TIME ( 16, 0, 0 ) ), ROW ( "Time", TIME ( 17, 0, 0 ) ), ROW ( "Time", TIME ( 18, 0, 0 ) ), ROW ( "Time", TIME ( 19, 0, 0 ) ), ROW ( "Time", TIME ( 20, 0, 0 ) ), ROW ( "Time", TIME ( 21, 0, 0 ) ), ROW ( "Time", TIME ( 22, 0, 0 ) ), ROW ( "Time", TIME ( 23, 0, 0 ) ), ROW ( "Time", TIME ( 24, 0, 0 ) ) ) ), "DateTime", [Date] + [Time], "Hour", HOUR ( [Time] ) ) return FILTER(k,NOW()-1 <=[DateTime] && [DateTime]<=NOW())
hanks,
But i open the pbix and i get the below, which isnt right, im UTC +8 and its 20:57 now.
Hi @uthall ,
Please refresh the table to check again.
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 |
---|---|
110 | |
95 | |
76 | |
65 | |
51 |
User | Count |
---|---|
146 | |
109 | |
106 | |
88 | |
61 |