Return a column for seconds starting from 07:00am

[Anonymous]

I want to return the seconds starting the count from 07:00am without restarting the count after every 60 seconds

So the results should look like this

[v-eqin-msft]

Hi @Anonymous ,

Since there are multiple conditions based on time  07:00:00(>, < , =), and you used DATEDIFF() twice with the same experssion, you could use a variable and SWITCH() to simplify the syntax:

_Counter = 
VAR _defaultime = TIME (7, 00, 00 )
VAR _diff = DATEDIFF ( _defaultime, muchData_MultipleBelts[Time], SECOND )

RETURN SWITCH (TRUE (),[Time] > _defaultime, _diff,[Time] < _defaultime,( 60 * 60 * 24 ) + _diff,BLANK ())

Output:

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[rocky09 · Accepted solution]

Check this one..
I just used time default time as 7:50:10 to test the Dax code. You can change it to 7:00:00

_Counter = 
var _defaultime = TIME(7,50,10)

return

IF('Table'[Time] >_defaultime,

DATEDIFF(_defaultime,'Table'[Time],SECOND),
BLANK()
)

[Anonymous]

I changed your dax code so at 00:00:00 the count of seconds continue untill 7:00:00

_Counter = 
var _defaultime = TIME(7,00,00)

return

IF(muchData_MultipleBelts[Time] >_defaultime,

DATEDIFF(_defaultime,muchData_MultipleBelts[Time],SECOND),

IF(muchData_MultipleBelts[Time] < _defaultime,

(60*60*24) + DATEDIFF(_defaultime, muchData_MultipleBelts[Time], SECOND),
BLANK()
))

Can you check if its correct? I mean it works but maybe there is a better way 

[rocky09]

Hi, Looks good.

[SpartaBI]

@Anonymous create this calculated column and make it a whole number / decimal number date type:

Column 2 = ('Table'[Column] - 1/86400 * 60 * 60 * 7.5) * 86400

In your example it looks like you meant 07:30:00 so I wrote 7.5
In case it's than replace it with 7.5 with 7

Showcase Report – Contoso By SpartaBI