Re: Text to Date measure

scaballerom · ‎12-21-2022

Hi,

I have the following expression:

StartingTimeDeviation =

VAR StartTime = SELECTEDVALUE('Fact Jobs'[job_starting_time])

Var AvgStartTime = TIMEVALUE([AvgStartingTimeR6M])

VAR Result = IF (AvgStartTime> StartTime, -DATEDIFF(StartTime,AvgStartTime,MINUTE), DATEDIFF(AvgStartTime,StartTime,MINUTE))

RETURN

Result

where

StartTime is a Time column

and

AvgStartingTimeR6M =

VAR NumberOfDays = 181

VAR MaxDay = MAX('Fact Jobs'[job_starting_datetime])

VAR MinDay = MaxDay-NumberOfDays

VAR FilterContext =

FILTER (

ALL('Fact Jobs'),

'Fact Jobs'[job_status]="Completed" &&

'Fact Jobs'[job_key] = SELECTEDVALUE('Fact Jobs'[job_key]) &&

'Fact Jobs'[job_starting_datetime] <= MaxDay && 'Fact Jobs'[job_starting_datetime] > MinDay)

VAR Result =

CALCULATE(FORMAT(AVERAGE('Fact Jobs'[job_starting_time]),"HH:mm:ss"),FilterContext)

RETURN

Result

which returns the type "Text".

When I try to put StartingTimeDeviation in a graph, even though is "Whole Number", I get the following message:

Could someone help me out, please?

Thanks,
Sara

scaballerom · ‎12-21-2022

Hi @Mahesh0016 ,

Here find a sample:

job_key	job_starting_time	AvgStartingTimeR6M
423273	00:00:08	00:00:14
402221	00:00:11	00:00:11
378328	00:00:13	00:18:27
80925	00:00:15	00:00:25
38509	00:00:17	00:07:09
423276	00:00:19	00:00:23
402223	00:00:20	00:00:31
378329	00:01:10	00:20:06
378331	00:01:28	00:26:29
414645	00:01:45	00:54:52
80926	00:02:03	00:02:51
80927	00:07:04	00:07:28
99963	01:00:17	01:00:10
82495	01:00:20	01:00:23
172133	01:00:36	01:00:39
172134	01:00:54	01:00:56
172135	01:03:06	01:03:07
191644	01:03:24	01:03:23
296185	05:00:03	05:00:09
318077	05:00:05	05:00:10
275130	05:00:08	05:00:10
296187	05:05:10	05:05:21
318079	05:05:11	05:05:37
275132	05:05:13	05:05:21
191179	06:15:06	06:15:07
126428	08:30:10	08:34:54
406503	08:30:28	08:30:24
408113	10:05:25	15:20:23
425312	10:47:26	10:47:26
425314	10:47:41	10:47:42
425316	10:50:58	10:50:58
425323	11:53:03	11:53:03
1590	18:00:04	18:00:07
1564	18:00:05	18:00:25
4164	18:00:05	18:00:09
13899	18:00:06	18:00:10
23925	18:00:08	18:00:11
29770	18:00:08	18:00:12
179579	18:00:09	18:00:12
1568	18:00:10	18:00:33
300153	18:00:11	18:02:23
1631	18:00:11	18:00:18
13902	18:00:13	18:00:18
23928	18:00:13	18:00:19
179582	18:00:13	18:00:20
162000	18:00:59	18:03:05
334412	18:01:00	17:53:29
334415	18:01:01	17:57:21
151596	18:05:02	18:05:08
23858	18:05:03	18:07:07
151599	18:05:04	18:05:10
1573	18:15:09	18:15:11
1578	18:30:10	18:07:27
191180	18:30:11	18:30:08
185853	18:30:12	18:29:31
1569	18:45:13	18:45:08
82514	18:45:14	18:45:08
207853	18:45:14	18:45:09
82517	18:45:15	18:45:11
134478	18:45:16	18:45:11
207856	18:45:17	18:45:14
36855	19:00:03	19:00:15
53999	19:00:04	18:50:30
79084	19:00:05	19:02:57
96849	19:00:06	18:55:28
28833	19:00:07	19:00:20
54002	19:00:08	18:54:15
79081	19:00:09	19:05:34
96850	19:01:10	18:56:22
96851	19:05:12	18:59:05
334403	19:05:28	18:53:11
137374	19:15:13	19:15:09
275135	19:15:15	18:14:53
137377	19:15:16	19:15:11
275138	19:15:16	19:03:56
36852	19:20:02	19:20:07
25138	19:20:03	19:20:11
8089	19:30:04	19:30:07
49289	19:30:04	19:30:08
71610	19:30:06	19:30:09
147009	19:30:07	19:30:11
340901	19:30:09	19:30:10
8092	19:30:09	19:30:17
71613	19:30:10	19:30:18
49291	19:46:28	19:36:38
13942	20:00:14	14:04:20
13962	20:00:15	20:00:10
99541	20:00:16	20:00:10
137390	20:00:17	20:00:11
186127	20:00:17	19:58:25
120469	20:00:20	20:01:49
13945	20:00:21	19:44:04
13965	20:00:22	20:00:20
78224	20:00:22	20:00:20
402226	20:00:24	17:48:44
137393	20:00:25	20:00:22
54005	20:00:25	20:00:18
402227	20:00:57	17:49:17
1615	20:02:28	20:03:40

The strange thing is that sometimes I can display the results on StartingTimeDeviation , but other times I cannot, and I don't find a pattern when this happens.