weird datetime format change in the middle of the data

[MarkH]

Given the following dax table, all dates in GB format,

Time - DST2 =

ADDCOLUMNS(

    ADDCOLUMNS(

    GENERATESERIES(

        DATE(2024, 1, 1), // Start date (January 1, 2024)

        DATE(2026, 12, 31), // End date (December 31, 2025)

        Time(1, 0, 0) // 1-hour increment (h,m,s)

    ),

    "BST",[Value]+(1/24) //value has been renamed the GMT column

,"StartSunday",

    VAR d = DATE( YEAR([Value]), 3, 1)  // Assuming March as an example

    VAR r = EOMONTH( d, 0 ) - MOD( WEEKDAY( EOMONTH( d, 0 ), 2 ), 7 )

    RETURN r

,"FinishSunday",

    VAR d = DATE( YEAR([Value]), 10, 1)  // Assuming March as an example

    VAR r = EOMONTH( d, 0 ) - MOD( WEEKDAY( EOMONTH( d, 0 ), 2 ), 7 )

    RETURN r

    ), //end of addcolumns

"DST",

    if([StartSunday]<[Value] && [FinishSunday]>[Value],"BST","GMT")

) //end of addcolumns

why does the format of the Value column suddenly change from 27/11/2024 09:00:00 to the next row being 27/11/2024 09:59:59 when I am expecting it to return 27/11/2024 10:00:00?

[OwenAuger · Accepted solution]

Hi @MarkH 

The short answer is that when using TIME(1,0,0) as the increment in GENERATESERIES, some numerical error is introduced since the values are converted to floating point in the course of the repeated summation.

Here's one way you could rewrite to get around this issue, with some variables added for clarity

(this will end at 23:00 rather than 0:00 on the last date though):

Time - DST2 =
VAR TimeValues =
    SELECTCOLUMNS ( GENERATESERIES ( 0, 23 ), "@Time", TIME ( [Value], 0, 0 ) )
VAR DateValues =
    GENERATESERIES (
    	DATE ( 2024, 1, 1 ), // Start date (January 1, 2024)
	    DATE ( 2026, 12, 31 ) // End date (December 31, 2025)
	)
VAR DateTimeValues =
    SELECTCOLUMNS (
        GENERATE (
            DateValues,
            TimeValues -- 0:00 to 23:00
        ),
        "Value", [Value] + [@Time]
    )
RETURN
    ADDCOLUMNS (
        ADDCOLUMNS (
			DateTimeValues,
            "BST",
                [Value] + ( 1 / 24 ) //value has been renamed the GMT column
                ,
            "StartSunday",
                VAR d =
                    DATE ( YEAR ( [Value] ), 3, 1 ) // Assuming March as an example
                VAR r =
                    EOMONTH ( d, 0 ) - MOD ( WEEKDAY ( EOMONTH ( d, 0 ), 2 ), 7 )
                RETURN
                    r,
            "FinishSunday",
                VAR d =
                    DATE ( YEAR ( [Value] ), 10, 1 ) // Assuming March as an example
                VAR r =
                    EOMONTH ( d, 0 ) - MOD ( WEEKDAY ( EOMONTH ( d, 0 ), 2 ), 7 )
                RETURN
                    r
        ),
        //end of addcolumns
        "DST",
            IF ( [StartSunday] < [Value] && [FinishSunday] > [Value], "BST", "GMT" )
    )
//end of addcolumns

Note: CROSSJOIN could also be used in place of GENERATE here.

Does this work for you?