Calculated column will not return values when updated via scheduled or manual refresh

[MOOREJEFFREY]

Calculated column works fine in desktop however if the data set is scheduled refresh then the error below is displayed:

WOrks fine when published from desktop. Problem is only after scheduled or manual refresh.

Calculated column:

duration =

VAR NextIndex = [Index] + 1

VAR NameNowArea = [Area]

VAR NameNowLine = [Line]

VAR NameNowWorkCenter = [Work Center]

VAR timest = CALCULATETABLE(

            VALUES ( 'CX4X Line Machine States'[_time] ),

            FILTER ( ALL ( 'CX4X Line Machine States' ), [Index] = NextIndex )

        )

RETURN IF(NOT(timest), (NOW() - [_time]) * 24.0 * 60.0,

    (timest - [_time]) * 24.0 * 60.0)

[GilbertQ]

Hi @MOOREJEFFREY 

I think this has to do with the function "NOW()" because all the PBI Service servers are in UTC. So it would appear that using NOW() is probably returning the wrong value which is causing it to fail.

It will work on PBI Desktop because this is in your current timezone, so now is accurate.

What you would have to do, is to take NOW() and add the hours for your timezone. If for example you are in GMT + 8 you would need to create a variable as shown below.

VAR CurrentDateTime = NOW() + TIME(8,0,0)

[MOOREJEFFREY · Accepted solution]

Hello GilbertQ,

 Thank you very much for the suggestion. I removed all references to NOW() in the query and have the same result. Have another solution for this problem however it should be lilely flagged as an instance where a calucation runs on the Vertipack engine but not in Analysis services. Thank you again for the feedback.

Best Regards

Jeff