Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Can't select Date Field in Formulas

Good afternoon,    I am trying to caluclate the difference between a start date and an end date. I am trying to use the  Datediff formula, but I am unable to select the start or end date in my dat...
Need Help
  • v-cherch-msft's avatar
    v-cherch-msft
    7 years ago

    Hi Anonymous 

    You may create the datediff measure as below.Then add a measure to get the correct total.

    DateDiff_seconds = DATEDIFF(MAX(Table1[Start Date]),MAX(Table1[End Date]),SECOND)
    DateDiff_seconds_2 = IF(NOT(ISBLANK(SUM(Table1[Start Date]))),DATEDIFF(MAX(Table1[Start Date]),MAX(Table1[End Date]),SECOND))
    Total = SUMX(Table1,[DateDiff_seconds])

    Regards,

Anonymous's avatar
Anonymous
Not applicable
7 years ago

I tried posting a sample of my PBIX file. but it would not let me. See below for my sample excel data. I've also included some pictures showing the formulas I am using.

 

 

 

BatchNumberStart DateEnd Date
16/4/2019 13:41 
12/27/2019 12:552/27/2019 12:55
13/18/2019 18:303/19/2019 8:55
1  
1 4/14/2019 10:04
1 4/14/2019 10:11
1 4/14/2019 10:27
1 5/7/2019 10:01
1 5/7/2019 10:01
1 5/7/2019 10:02
1 5/14/2019 15:21
1 5/28/2019 12:46
1 5/29/2019 9:20
11/2/2019 10:351/2/2019 12:39
11/2/2019 15:511/3/2019 12:53
11/2/2019 15:511/3/2019 12:59
11/2/2019 15:511/3/2019 13:09
11/2/2019 15:511/3/2019 13:10
11/2/2019 16:171/2/2019 17:26
11/2/2019 16:241/2/2019 17:29
11/3/2019 16:111/3/2019 17:02
11/3/2019 16:291/3/2019 17:07
11/3/2019 17:501/4/2019 3:10
11/3/2019 17:521/3/2019 20:55
11/3/2019 17:521/4/2019 3:28
11/4/2019 10:331/4/2019 13:02
11/4/2019 13:341/5/2019 10:02
11/4/2019 13:341/5/2019 10:19
11/4/2019 13:341/5/2019 10:24
11/4/2019 15:421/5/2019 8:14
11/4/2019 15:551/5/2019 8:17
11/5/2019 22:261/6/2019 10:41
11/5/2019 22:261/6/2019 11:37
11/6/2019 12:221/6/2019 23:39
11/6/2019 12:231/6/2019 23:27
11/6/2019 12:241/6/2019 22:41
11/6/2019 12:241/6/2019 22:44
11/6/2019 12:241/6/2019 22:46
11/6/2019 12:241/6/2019 23:41
11/6/2019 12:241/7/2019 0:17
11/6/2019 15:021/6/2019 15:22
11/7/2019 11:321/7/2019 12:08
11/7/2019 11:371/7/2019 12:07
11/7/2019 16:191/7/2019 16:58
11/7/2019 20:121/8/2019 9:07
11/7/2019 20:231/8/2019 9:09
11/7/2019 20:291/8/2019 11:51
11/7/2019 20:291/8/2019 14:40
11/7/2019 20:291/8/2019 14:58
11/7/2019 20:291/8/2019 15:36
11/7/2019 20:491/8/2019 10:11
11/7/2019 20:491/8/2019 12:54
11/7/2019 20:501/8/2019 11:20
11/7/2019 20:511/8/2019 9:30
11/7/2019 21:201/8/2019 2:25
11/8/2019 8:121/8/2019 9:03
11/8/2019 10:171/8/2019 10:52
11/8/2019 10:181/8/2019 10:54
11/8/2019 12:551/8/2019 14:14
11/8/2019 14:101/8/2019 15:04
11/8/2019 23:101/9/2019 10:00
11/8/2019 23:101/9/2019 15:47
11/8/2019 23:101/10/2019 2:32
11/8/2019 23:101/10/2019 12:35
11/8/2019 23:101/11/2019 8:42
11/8/2019 23:101/11/2019 8:43
11/8/2019 23:101/11/2019 14:45
11/8/2019 23:101/11/2019 15:26
11/8/2019 23:101/11/2019 15:53
11/8/2019 23:101/12/2019 3:55
11/9/2019 7:141/9/2019 8:49
11/9/2019 12:191/9/2019 15:34
11/10/2019 12:331/11/2019 8:31
11/10/2019 16:371/11/2019 16:17
11/10/2019 16:371/11/2019 16:25
11/10/2019 16:381/11/2019 16:16
11/11/2019 9:041/11/2019 11:07
11/11/2019 9:381/11/2019 11:04
11/12/2019 20:401/12/2019 13:20
11/12/2019 20:521/12/2019 13:26
11/13/2019 19:421/13/2019 12:37
11/14/2019 8:181/14/2019 8:40
11/14/2019 8:291/14/2019 8:54
11/14/2019 12:051/14/2019 12:58
11/14/2019 14:441/14/2019 16:13
11/14/2019 15:391/15/2019 11:51
11/14/2019 15:391/15/2019 16:32
11/14/2019 15:401/15/2019 12:01
11/14/2019 15:401/15/2019 15:41
11/14/2019 15:411/15/2019 11:43
11/14/2019 15:421/15/2019 13:54
11/14/2019 15:421/15/2019 16:07
11/14/2019 16:581/15/2019 7:44
11/15/2019 9:551/15/2019 10:51
11/15/2019 10:421/15/2019 18:08
11/15/2019 10:421/15/2019 18:24
11/15/2019 10:431/15/2019 18:17
11/15/2019 10:431/15/2019 18:35
11/15/2019 10:441/15/2019 18:29
11/15/2019 10:441/15/2019 18:34
11/15/2019 10:441/15/2019 18:36
11/15/2019 10:451/15/2019 18:38
11/16/2019 8:111/17/2019 7:39
11/16/2019 8:111/17/2019 8:34
11/16/2019 8:111/17/2019 11:13
11/16/2019 8:111/17/2019 11:42
11/16/2019 8:111/17/2019 14:41
11/16/2019 8:111/17/2019 15:40
11/16/2019 8:111/17/2019 15:45
11/16/2019 8:111/17/2019 16:42
11/16/2019 8:111/17/2019 18:23
11/16/2019 8:111/18/2019 9:15
11/16/2019 8:111/18/2019 9:17
11/16/2019 8:111/18/2019 9:51
11/16/2019 8:111/18/2019 11:58
11/16/2019 8:111/18/2019 12:49
11/16/2019 8:111/19/2019 3:49
11/16/2019 8:111/19/2019 9:03
11/17/2019 9:471/17/2019 12:26
11/17/2019 9:481/17/2019 12:25
11/17/2019 14:411/17/2019 17:55
11/17/2019 14:431/17/2019 16:28
11/17/2019 17:551/18/2019 9:12
11/17/2019 17:551/18/2019 9:14
11/17/2019 17:581/18/2019 9:02
11/17/2019 17:581/18/2019 9:10
11/17/2019 20:171/17/2019 21:08
11/18/2019 9:571/18/2019 10:26
11/18/2019 10:251/18/2019 11:55
11/18/2019 10:411/18/2019 11:21
11/18/2019 13:221/18/2019 14:28
11/18/2019 17:221/18/2019 17:53
11/21/2019 15:581/22/2019 1:17
11/22/2019 15:201/22/2019 15:43
11/22/2019 16:161/22/2019 20:12
11/22/2019 16:161/22/2019 20:40
11/22/2019 16:161/22/2019 20:50
11/24/2019 10:541/24/2019 14:45
11/24/2019 12:151/24/2019 15:33
11/24/2019 12:441/24/2019 17:09
11/24/2019 12:451/24/2019 16:11
11/24/2019 12:461/24/2019 16:23
11/24/2019 14:531/24/2019 17:08
11/24/2019 14:541/24/2019 15:59
11/24/2019 14:551/24/2019 17:01
11/24/2019 14:561/24/2019 16:48
11/24/2019 15:151/24/2019 16:58
11/25/2019 10:351/25/2019 11:20
11/25/2019 16:271/25/2019 17:12
11/27/2019 14:411/27/2019 15:54
11/28/2019 11:421/28/2019 12:19
11/28/2019 13:061/28/2019 15:51
11/28/2019 13:231/28/2019 16:26
11/28/2019 13:241/28/2019 16:34
11/28/2019 13:371/28/2019 16:09
11/28/2019 13:371/28/2019 16:19
11/28/2019 15:091/28/2019 16:46
11/28/2019 15:131/28/2019 16:54
11/29/2019 15:441/29/2019 17:17
11/30/2019 8:581/30/2019 9:53
11/30/2019 8:581/30/2019 11:22
11/30/2019 15:501/30/2019 17:44
11/31/2019 12:161/31/2019 12:51
11/31/2019 12:171/31/2019 13:12
11/31/2019 12:171/31/2019 13:27
11/31/2019 12:171/31/2019 14:01
11/31/2019 12:181/31/2019 13:18
11/31/2019 12:191/31/2019 15:22
11/31/2019 12:261/31/2019 14:06
11/31/2019 12:271/31/2019 14:24
11/31/2019 15:051/31/2019 16:50
11/31/2019 15:381/31/2019 17:31
11/31/2019 15:421/31/2019 17:00
12/1/2019 11:202/1/2019 14:13
12/1/2019 11:202/1/2019 15:24
12/1/2019 11:202/2/2019 13:43
12/1/2019 11:202/3/2019 11:31
12/1/2019 11:462/2/2019 10:03
12/1/2019 11:462/2/2019 10:13
12/1/2019 11:462/2/2019 16:17
12/1/2019 11:462/3/2019 8:24
12/1/2019 13:072/2/2019 10:49
12/1/2019 14:452/2/2019 16:31
12/1/2019 14:452/3/2019 8:28
12/1/2019 14:452/3/2019 8:46
12/1/2019 14:452/3/2019 10:06
12/1/2019 14:452/4/2019 12:03
12/1/2019 15:002/2/2019 9:44
12/1/2019 15:002/2/2019 13:27
12/1/2019 15:002/2/2019 15:08
12/1/2019 15:002/2/2019 15:51
12/4/2019 11:062/4/2019 12:10
12/4/2019 11:062/4/2019 12:11
12/4/2019 15:512/4/2019 16:16
12/4/2019 16:542/4/2019 17:05
12/5/2019 8:232/5/2019 14:46
12/5/2019 8:232/5/2019 17:27
12/6/2019 16:432/6/2019 17:39
12/6/2019 16:432/6/2019 17:40
12/6/2019 16:432/6/2019 17:41
12/6/2019 16:432/6/2019 17:41
12/6/2019 16:442/6/2019 17:42
12/6/2019 16:442/6/2019 17:42
12/6/2019 16:442/6/2019 17:42
12/7/2019 7:582/7/2019 15:02
12/7/2019 10:302/7/2019 12:51
12/7/2019 14:002/7/2019 17:31
12/7/2019 15:462/7/2019 17:21
12/7/2019 16:332/7/2019 17:39
12/11/2019 9:522/11/2019 12:35
12/11/2019 9:522/11/2019 12:37
12/11/2019 9:522/11/2019 12:38
12/11/2019 14:002/11/2019 17:21
12/11/2019 14:002/11/2019 21:12
12/11/2019 18:292/11/2019 18:46
12/12/2019 10:142/12/2019 11:37
12/12/2019 10:152/12/2019 11:38
12/12/2019 10:152/12/2019 11:41
12/12/2019 17:062/13/2019 7:47
12/12/2019 17:062/13/2019 8:11
12/12/2019 17:062/13/2019 9:18
12/12/2019 17:072/13/2019 8:38
12/12/2019 17:072/13/2019 9:20
12/12/2019 17:072/13/2019 9:32
12/12/2019 17:082/13/2019 9:21
12/13/2019 12:122/13/2019 17:04
12/13/2019 12:482/13/2019 15:08
12/13/2019 12:482/13/2019 18:19
12/13/2019 12:492/13/2019 16:34
12/13/2019 12:492/13/2019 17:08
12/13/2019 12:542/13/2019 17:39
12/13/2019 13:092/13/2019 16:24
12/13/2019 13:102/13/2019 16:24
12/13/2019 13:112/13/2019 15:49
12/13/2019 14:272/13/2019 17:48
12/13/2019 14:442/13/2019 18:13
12/13/2019 14:452/13/2019 17:59
12/13/2019 15:102/13/2019 17:30
12/13/2019 15:102/13/2019 17:31
12/13/2019 15:502/13/2019 16:44
12/14/2019 13:322/14/2019 15:16
12/14/2019 13:342/14/2019 14:53
12/14/2019 15:212/14/2019 16:39
12/14/2019 15:212/14/2019 17:24
12/14/2019 15:222/14/2019 17:47
12/14/2019 15:222/15/2019 9:23
12/14/2019 15:232/15/2019 9:28
12/14/2019 15:232/15/2019 9:41
12/14/2019 15:232/15/2019 12:19
12/14/2019 15:242/15/2019 12:22
12/14/2019 15:242/15/2019 13:28
12/14/2019 15:252/15/2019 10:14
12/14/2019 18:012/15/2019 8:26
v-cherch-msft's avatar
v-cherch-msft
Microsoft Employee
7 years ago

Hi Anonymous 

You may create the datediff measure as below.Then add a measure to get the correct total.

DateDiff_seconds = DATEDIFF(MAX(Table1[Start Date]),MAX(Table1[End Date]),SECOND)
DateDiff_seconds_2 = IF(NOT(ISBLANK(SUM(Table1[Start Date]))),DATEDIFF(MAX(Table1[Start Date]),MAX(Table1[End Date]),SECOND))
Total = SUMX(Table1,[DateDiff_seconds])

Regards,

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    That worked! Thank you so much for your continued help!