Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
Anonymous
Not applicable

Can't select Date Field in Formulas

‎06-10-2019 04:38 PM

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 dataset within the formula bar. The two fields are both in the "Date" format, and I can select them when using aggregate functions, such as "Sum" or "Max", but not in the Datediff formula. What is the reason for this?

 

Thank you for your assistance. 

Labels:
Message 1 of 14
4,020 Views
0
1 ACCEPTED SOLUTION
v-cherch-msft
Employee
Employee
In response to Anonymous

‎06-18-2019 06:59 PM

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,

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Can't select Date.pbix
Message 13 of 14
4,062 Views
0
13 REPLIES 13
v-cherch-msft
Employee
Employee

‎06-10-2019 11:10 PM

Hi @Anonymous 

You may use aggregate function in the measure formula like below:

Datediff = DATEDIFF(MAX(DateTable[Start date]),MAX(DateTable[End date]),DAY)

1.png

Regards,

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Message 2 of 14
3,929 Views
0
Anonymous
Not applicable
In response to v-cherch-msft

‎06-11-2019 10:39 AM

Thank you. How would I get it to show the time difference in Hours? I change the final argument from Days to Hours, and that seems to work, but it rounds the hours, I'd like it to include minutes as well. 

Message 3 of 14
3,925 Views
0
v-cherch-msft
Employee
Employee
In response to Anonymous

‎06-11-2019 06:42 PM

Hi @Anonymous 

You may have a look at DATEDIFF function.

Datediff = DATEDIFF(MAX(DateTable[Start date]),MAX(DateTable[End date]),MINUTE)

1.png

Regards,

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Message 4 of 14
3,919 Views
Anonymous
Not applicable
In response to v-cherch-msft

‎06-12-2019 09:11 AM

Thank you for your response. However, it does not quite solve my problem. I need the output of the formula to be in the "HH:MM" format. 

Message 5 of 14
3,915 Views
0
v-cherch-msft
Employee
Employee
In response to Anonymous

‎06-12-2019 07:04 PM

Hi @Anonymous 

You may try below measures.As 24 hours could be supported in Time format in power bi.If the hours is over 24,you may use below measure to get it in text format.

Datediff = DATEDIFF(MAX(DateTable[Start date]),MAX(DateTable[End date]),SECOND)
hour>24 = var hours=TRUNC([Datediff]/3600) var minutes=TRUNC(MOD([Datediff],3600)/60) return hours&":"&minutes

If hour<24,time format:

Hour<24 = TIME(0,0,0)+[Datediff]

1.png

Regards,

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Message 6 of 14
3,905 Views
0
Anonymous
Not applicable
In response to v-cherch-msft

‎06-13-2019 09:32 AM

This does not seem to be working - The Datediff column goes blank when I add the hour>24 column.

 

These are my results:

 

Capture44.PNGCapture555.PNG

 

 

 

Message 7 of 14
3,901 Views
0
v-cherch-msft
Employee
Employee
In response to Anonymous

‎06-13-2019 11:56 PM

Hi @Anonymous 

Please create datediff measure with the above formula.

1.png

Regards,

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Message 8 of 14
3,896 Views
0
Anonymous
Not applicable
In response to v-cherch-msft

‎06-14-2019 03:20 PM

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? 

Message 9 of 14
3,893 Views
0
v-cherch-msft
Employee
Employee
In response to Anonymous

‎06-16-2019 06:46 PM

Hi @Anonymous 

Please post some sample data which could reproduce your scenario.

Regards,

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Message 11 of 14
3,131 Views
0
Anonymous
Not applicable
In response to v-cherch-msft

‎06-18-2019 12:11 PM

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.

 

Capture6.18.19.PNG

 

 

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
Message 12 of 14
3,127 Views
0
v-cherch-msft
Employee
Employee
In response to Anonymous

‎06-18-2019 06:59 PM

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,

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Can't select Date.pbix
Message 13 of 14
4,063 Views
0
Anonymous
Not applicable
In response to v-cherch-msft

‎06-21-2019 12:28 PM

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

Message 14 of 14
3,105 Views
0
Ashish_Mathur
Super User
Super User
In response to Anonymous

‎06-14-2019 08:29 PM

Hi,

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Message 10 of 14
3,887 Views
0

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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

View All