List.Dates: Date start from 0700-0659

[vincentakatoh]

Hi, 

Used Blank Query (=List.Dates) to create a table Dim.Date. With Calculated Column, 

Day = DimDate[Date].[Day]

Que: How to change above Dax to starts each day from 0700- 0700 (following day). The current PBI default is from 0000-2359.

Actual data has a column TestTime

TestTime

1/1/2010 23:57

1/1/2010 3:50

1/1/2010 3:50

1/1/2010 3:48

1/1/2010 3:47

1/1/2010 6:01

1/1/2010 22:13

1/1/2010 22:11

1/1/2010 22:15

1/1/2010 0:37

1/1/2010 0:40

1/1/2010 0:40

1/1/2010 1:57

[v-yulgu-msft]

Hi @vincentakatoh,

What is your source data? What is your expected output? Do you want to achieve your requirement using DAX or Power Query?

Below is the result I have got using Power Query to create a datetime table, each day starts from 7:00 AM.

1. Use blank query (List.Dates) to create a date list
2. Change the list to table
3. Use List.Times to create a time column
4. Merge the date column and time column to generate a datetime field

let
    Source = List.Dates(#date(2010, 1, 1), 3, #duration(1, 1, 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(7, 0, 0), 6, #duration(0, 4, 0, 0))),
    #"Expanded TimeColumn1" = Table.ExpandListColumn(#"Added Custom", "TimeColumn"),
    #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Expanded TimeColumn1", {{"Column1", type text}, {"TimeColumn", type text}}, "en-US"),{"Column1", "TimeColumn"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"DateTime")
in
    #"Merged Columns"

                   

Best regards,
Yuliana Gu

[vincentakatoh]

Hi @v-yulgu-msft, 

Thanks.

1) Data source: folder (w multiple csv). 

2) Expected output: Create a slicer for "Day". When "7-Jul"  is selected, the time period is from 7-Jul 0700- 8-Jul-0659. 

3) Using Calculated Column (DAX) to create Column Day. 

I use below DAX to create DAY but failed. Can advise how to correct?

Day = DimDate[DateTime].[Day]

List of Calculated Column that I intend to add. 

Year = 'DimDate'[Date].[Year]

QuarterNo = 'DimDate'[Date].[QuarterNo]

Quarter = DimDate[Date].[Quarter]

MonthNo = DimDate[Date].[MonthNo]

Month = DimDate[Date].[Month]

Day = DimDate[Date].[Day]

FiscalDate = DATEADD(DimDate[Date],2,QUARTER)

FiscalYear = DimDate[FiscalDate].[Year]

FiscalQuarter = DimDate[FiscalDate].[QuarterNo]

WEEKNUM = WEEKNUM(DimDate[Date],1)

[v-yulgu-msft · Accepted solution]

Hi @vincentakatoh,

In calendar date table, you need two calculated columns:

Yestoday = DATEADD('Dim Date'[Date],-1,DAY)
Day = 'Dim Date'[Date].[Day]

In actual source table which contains the [TestTime] column, you need below calculated columns. Remember to change the date type to Date or Time as the returned values are text if using FORMAT to extract date or time part from datetime.

Date from testTime = FORMAT(testTime[TestTime],"m/d/yyyy")
Time from testTime = FORMAT(testTime[TestTime],"hh:mm:ss")

New day =
IF (
    testTime[Time from testTime] < TIME ( 7, 0, 0 ),
    LOOKUPVALUE (
        'Dim Date'[Yestoday],
        'Dim Date'[Date], testTime[Date from testTime]
    ),
    testTime[Date from testTime]
)       

Create a one to many relationship between calendar table and source table.

 

Regards,
Yuliana Gu