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,

v-cherch-msft's avatar
v-cherch-msft
Microsoft Employee
7 years ago

Hi Anonymous 

Please create datediff measure with the above formula.

Regards,

Anonymous's avatar
Anonymous
Not applicable
7 years ago

This worked, thank you. However, I need to be able to sum the the total for each entry, but since we used Max in the DateDiff formula, the total is coming out as the highest value in the data. So in your example, the sum would be "4:17" when in fact I need it to be "28:17", the sum of the difference. I changed our DateDiff formula from Max to Sum, but this throws the error "An invalid numeric representation of a date value was encountered". I've been researching, and my guess is that it's being caused by some blanks in the data. How would I get around that error? 

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    7 years ago

    Hi,

    Share the link from where i can download your PBI file.

  • v-cherch-msft's avatar
    v-cherch-msft
    Microsoft Employee
    7 years ago

    Hi Anonymous 

    Please post some sample data which could reproduce your scenario.

    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!