Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
scaballerom
Helper I
Helper I

Text to Date measure

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:
scaballerom_1-1671621056475.png

 

scaballerom_0-1671621032166.png

 

Could someone help me out, please?

 

Thanks,
Sara

2 REPLIES 2
scaballerom
Helper I
Helper I

Hi @Mahesh0016 ,

 

Here find a sample: 

job_keyjob_starting_timeAvgStartingTimeR6M
42327300:00:0800:00:14
40222100:00:1100:00:11
37832800:00:1300:18:27
8092500:00:1500:00:25
3850900:00:1700:07:09
42327600:00:1900:00:23
40222300:00:2000:00:31
37832900:01:1000:20:06
37833100:01:2800:26:29
41464500:01:4500:54:52
8092600:02:0300:02:51
8092700:07:0400:07:28
9996301:00:1701:00:10
8249501:00:2001:00:23
17213301:00:3601:00:39
17213401:00:5401:00:56
17213501:03:0601:03:07
19164401:03:2401:03:23
29618505:00:0305:00:09
31807705:00:0505:00:10
27513005:00:0805:00:10
29618705:05:1005:05:21
31807905:05:1105:05:37
27513205:05:1305:05:21
19117906:15:0606:15:07
12642808:30:1008:34:54
40650308:30:2808:30:24
40811310:05:2515:20:23
42531210:47:2610:47:26
42531410:47:4110:47:42
42531610:50:5810:50:58
42532311:53:0311:53:03
159018:00:0418:00:07
156418:00:0518:00:25
416418:00:0518:00:09
1389918:00:0618:00:10
2392518:00:0818:00:11
2977018:00:0818:00:12
17957918:00:0918:00:12
156818:00:1018:00:33
30015318:00:1118:02:23
163118:00:1118:00:18
1390218:00:1318:00:18
2392818:00:1318:00:19
17958218:00:1318:00:20
16200018:00:5918:03:05
33441218:01:0017:53:29
33441518:01:0117:57:21
15159618:05:0218:05:08
2385818:05:0318:07:07
15159918:05:0418:05:10
157318:15:0918:15:11
157818:30:1018:07:27
19118018:30:1118:30:08
18585318:30:1218:29:31
156918:45:1318:45:08
8251418:45:1418:45:08
20785318:45:1418:45:09
8251718:45:1518:45:11
13447818:45:1618:45:11
20785618:45:1718:45:14
3685519:00:0319:00:15
5399919:00:0418:50:30
7908419:00:0519:02:57
9684919:00:0618:55:28
2883319:00:0719:00:20
5400219:00:0818:54:15
7908119:00:0919:05:34
9685019:01:1018:56:22
9685119:05:1218:59:05
33440319:05:2818:53:11
13737419:15:1319:15:09
27513519:15:1518:14:53
13737719:15:1619:15:11
27513819:15:1619:03:56
3685219:20:0219:20:07
2513819:20:0319:20:11
808919:30:0419:30:07
4928919:30:0419:30:08
7161019:30:0619:30:09
14700919:30:0719:30:11
34090119:30:0919:30:10
809219:30:0919:30:17
7161319:30:1019:30:18
4929119:46:2819:36:38
1394220:00:1414:04:20
1396220:00:1520:00:10
9954120:00:1620:00:10
13739020:00:1720:00:11
18612720:00:1719:58:25
12046920:00:2020:01:49
1394520:00:2119:44:04
1396520:00:2220:00:20
7822420:00:2220:00:20
40222620:00:2417:48:44
13739320:00:2520:00:22
5400520:00:2520:00:18
40222720:00:5717:49:17
161520:02:2820: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.

Mahesh0016
Super User
Super User

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

Helpful resources

Announcements
Sept PBI Carousel

Power BI Monthly Update - September 2024

Check out the September 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Sept NL Carousel

Fabric Community Update - September 2024

Find out what's new and trending in the Fabric Community.