Need to set a report date on a row with both start and end times, while in DirectQuery mode

[macejason]

Hello,

I have a table in DirectQuery mode that keeps track of a timeline. Our report days roll at 6 AM each day. I want to generate a column called 'Report Date' on each data row that takes the ending date/time and properly figures out the report date. 

My first attempt was a calculated column with a simple IF(TIMEVALUE(ENDDATE)>TIME(6,0,0), ... , but I get an error about using TIMEVALUE in directquery mode.

An example of the data is below, with what I'd like report date to be so that I can properly calculate and sum things using my calendar table.

StartDate	EndDate	Comment	Duration	ReportDate
1/1/2023 6:00	1/1/2023 7:00	XYZ	1	1/1/2023
1/1/2023 18:00	1/1/2023 19:30	ABC	1.5	1/1/2023
1/2/2023 4:00	1/2/2023 6:30	LMNO	2.5	1/2/2023

[MatthRichardsUK]

Sorry for the mistake in my previous solution. The DATEADD function should be applied to a column rather than a single value.

To resolve this issue, you can modify the measure to use a column reference instead of a single value. Here is an example of how you could do this:

Report Date =
VAR CurrentEndDate = MAX(Table[EndDate])
RETURN
IF(TIME(HOUR(CurrentEndDate), MINUTE(CurrentEndDate), SECOND(CurrentEndDate)) > TIME(6,0,0),
DATEADD(Table[EndDate], -1, DAY),
Table[EndDate]
)

This measure should work as you expect, and it should return the correct report date for each row in your table.

I hope this helps!

[grantsamborn]

"In order to use any time intelligence calculation, you need a well-formed date table."

https://dax.guide/dateadd/

I'm assuming that means the column referred to by DATEADD must be the date column of the well-formed date table.

Please correct me if I'm wrong.

[macejason]

Error Message:
MdxScript(Model) (14, 89) Calculation error in measure 'INTERVAL_V1'[Report Date]: A date column containing duplicate dates was specified in the call to function 'DATEADD'. This is not supported.

From the following measure:

Report Date =

VAR CurrentEndDate = MAX(INTERVAL_V1[ENDDATE])

RETURN

IF(TIME(HOUR(CurrentEndDate),MINUTE(CurrentEndDate),SECOND(CurrentEndDate))>TIME(6,0,0),DATEADD(INTERVAL_V1[ENDDATE],-1,DAY),CurrentEndDate)

[MatthRichardsUK]

One option you could try is to create a measure that calculates the report date based on the end date. Here is an example of a measure that you could use:

Report Date =
VAR CurrentEndDate = MAX(Table[EndDate])
RETURN
IF(TIME(HOUR(CurrentEndDate), MINUTE(CurrentEndDate), SECOND(CurrentEndDate)) > TIME(6,0,0),
DATEADD(CurrentEndDate, -1, DAY),
CurrentEndDate
)

This measure uses the MAX function to get the latest end date in the table. It then checks if the time of the end date is after 6 AM. If it is, it subtracts 1 day from the end date to get the report date. If it is not, it returns the end date as the report date.

You can then use this measure in your report just like any other column. It should give you the report date for each row in your table.

[macejason]

Matt, 

Thanks for the shot. For some reason it expects a column instead of a single value in the DATEADD() section in the TRUE part of the IF statement.