Add Minutes to DateTime (but only over working hours)

[Anonymous]

All,

I receive the following error:

"Query (21, 20) Table variable 'TopValues' cannot be used in current context because a base table is expected."

when executing the below expression:

// This expression adds minutes to a datetime, but only distributes them over specified work days (mon-fri) and work hours (9-5).

EVALUATE

VAR startDatetime = DATE ( 2018, 12, 28 ) + TIME ( 16, 0, 0 )
    
VAR MinutesToAdd = 720

VAR Range = GENERATESERIES ( startDatetime, startDatetime + 90, TIME ( 0, 1, 0 ) ) // the 90 days is an abritary number.
    
VAR FilteredRange =
    FILTER (
        Range,
        NOT WEEKDAY ( [Value] ) IN { 1, 7 } // Include only weekdays
            && HOUR ( [Value] ) >= 9 && HOUR ( [Value] ) < 17 // 9 AM to 5 PM
            && NOT ( MONTH ( [Value] ) = 12 && DAY ( [Value] ) = 25 ) // Exclude Christmas
    )
    
VAR TopValues = TOPN ( MinutesToAdd, FilteredRange, [Value], ASC )
    
VAR Result = MAX ( TopValues[Value] )

RETURN
    Result

Any thoughts on a solution?

[Anonymous · Accepted solution]

Fixed it.  For some reason, changing MAX to MAXX works:

EVALUATE
{
VAR startDatetime = DATE ( 2018, 12, 28 ) + TIME ( 16, 0, 0 )
    
VAR MinutesToAdd = 720

VAR Range = GENERATESERIES ( startDatetime, startDatetime + 90, TIME ( 0, 1, 0 ) ) // the 90 days is an abritary number.
    
VAR FilteredRange =
    FILTER (
        Range,
        NOT WEEKDAY ( [Value] ) IN { 1, 7 } // Include only weekdays
            && HOUR ( [Value] ) >= 9 && HOUR ( [Value] ) < 17 // 9 AM to 5 PM
            && NOT ( MONTH ( [Value] ) = 12 && DAY ( [Value] ) = 25 ) // Exclude Christmas
    )
    
VAR TopValues = TOPN ( MinutesToAdd, FilteredRange, [Value], ASC )
    
VAR Result = MAXX ( TopValues, [Value] ) // Must be changed to MAXX

RETURN
    Result
    }

[Greg_Deckler]

Right, TOPN requires a Table, not a Table or Table Expression. 

https://docs.microsoft.com/en-us/dax/topn-function-dax

I'm not entirely certain I understand your need for that since the very next line you just grab the MAX. Just grab the MAX of your FilteredRange?

[Anonymous]

The TOPN (in conjunction with MAX()) is required for the described business logic to work (i.e., add time to a datetime but only have it apply to work days and work hours).  As an alternative to TOPN and MAX(), I may have added an index and then applied FILTER() to return [value] where [index] = MinutesToAdd, but TOPN is an elegant solution with an optimized query plan.

Anyhoo, this point is moot because TOPN is not the issue. You can confirm this in DAX Studio by evaluating that variable, i.e., this works:

VAR TopValues = TOPN ( MinutesToAdd, FilteredRange, [Value], ASC ) 
//VAR Result = MAX ( TopValues[Value] )
RETURN
TopValues