New Blank Table for last 24hrs from now

[uthall]

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"

[v-frfei-msft · Accepted 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())

[uthall]

hanks,

But i open the pbix and i get the below, which isnt right, im UTC +8 and its 20:57 now.

[v-frfei-msft]

Hi @uthall ,

Please refresh the table to check again.