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: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
gladies123
Helper I
Helper I

Reg: same date occurring multiple times need to display expected hours single time

‎04-01-2021 11:32 AM

hi,

 

Need a help to display a expected hours  which is occurring multiple times but it should be displayed once for those similar dates. i have attached picture for your reference.

 

note:i have shared a picture which i randomly created in excel for your reference but it should be displyed in power bi

 

thanks 

GladisIssue.PNG

 

Labels:
Message 1 of 38
1,867 Views
0
1 ACCEPTED SOLUTION
Syndicate_Admin
Administrator
Administrator

‎04-06-2021 02:31 AM

Don't @gladies123,

First create an index column;

Next, create 4 columns as follows:

Time = 
var _search=SEARCH("(",'Table'[jira entry date],1,0)
Return
MID('Table'[jira entry date],_search+1,6)
Date = 
var _search=SEARCH("(",'Table'[jira entry date],1,0)
Return
LEFT('Table'[jira entry date],_search-1)
_Hour = LEFT('Table'[Actual Hours],SEARCH("hr",'Table'[Actual Hours],1,0)-1)
Expected Hours = 
var _latertime=CALCULATE(MAX('Table'[Time]),FILTER('Table','Table'[Index]>EARLIER('Table'[Index])&&'Table'[Date]=EARLIER('Table'[Date])))
Return
IF(DATEDIFF('Table'[Time],_latertime,HOUR)<=3 && 'Table'[Index]=MINX(FILTER('Table','Table'[Date]=EARLIER('Table'[Date])),'Table'[Index]),CALCULATE(SUM('Table'[_Hour]),FILTER('Table','Table'[Index]>=EARLIER('Table'[Index])&&'Table'[Date]=EARLIER('Table'[Date]))),BLANK())

And you'll see:

v-kelly-msft_0-1617699142176.png

For the related .pbix file, see pls Attachment.

Saludos
Kelly

Did I answer your question? Mark my position as a solution!

View solution in original post

same date occurring multiple times need to display expected hours single time.pbix
Message 18 of 38
1,710 Views
0
37 REPLIES 37
Syndicate_Admin
Administrator
Administrator

‎04-06-2021 03:29 AM

Don't @gladies123,

First create an index column;

Next, create 4 columns as follows:

Time = 
var _search=SEARCH("(",'Table'[jira entry date],1,0)
Return
MID('Table'[jira entry date],_search+1,6)
Date = 
var _search=SEARCH("(",'Table'[jira entry date],1,0)
Return
LEFT('Table'[jira entry date],_search-1)
_Hour = LEFT('Table'[Actual Hours],SEARCH("hr",'Table'[Actual Hours],1,0)-1)
Expected Hours = 
var _latertime=CALCULATE(MAX('Table'[Time]),FILTER('Table','Table'[Index]>EARLIER('Table'[Index])&&'Table'[Date]=EARLIER('Table'[Date])))
Return
IF(DATEDIFF('Table'[Time],_latertime,HOUR)<=3 && 'Table'[Index]=MINX(FILTER('Table','Table'[Date]=EARLIER('Table'[Date])),'Table'[Index]),CALCULATE(SUM('Table'[_Hour]),FILTER('Table','Table'[Index]>=EARLIER('Table'[Index])&&'Table'[Date]=EARLIER('Table'[Date]))),BLANK())

And you'll see:

v-kelly-msft_0-1617699142176.png

For the related .pbix file, see pls Attachment.

Saludos
Kelly

Did I answer your question? Mark my position as a solution!

same date occurring multiple times need to display expected hours single time.pbix
Message 38 of 38
1,098 Views
0
Syndicate_Admin
Administrator
Administrator

‎04-06-2021 03:25 AM

Don't @gladies123,

First create an index column;

Next, create 4 columns as follows:

Time = 
var _search=SEARCH("(",'Table'[jira entry date],1,0)
Return
MID('Table'[jira entry date],_search+1,6)
Date = 
var _search=SEARCH("(",'Table'[jira entry date],1,0)
Return
LEFT('Table'[jira entry date],_search-1)
_Hour = LEFT('Table'[Actual Hours],SEARCH("hr",'Table'[Actual Hours],1,0)-1)
Expected Hours = 
var _latertime=CALCULATE(MAX('Table'[Time]),FILTER('Table','Table'[Index]>EARLIER('Table'[Index])&&'Table'[Date]=EARLIER('Table'[Date])))
Return
IF(DATEDIFF('Table'[Time],_latertime,HOUR)<=3 && 'Table'[Index]=MINX(FILTER('Table','Table'[Date]=EARLIER('Table'[Date])),'Table'[Index]),CALCULATE(SUM('Table'[_Hour]),FILTER('Table','Table'[Index]>=EARLIER('Table'[Index])&&'Table'[Date]=EARLIER('Table'[Date]))),BLANK())

And you'll see:

v-kelly-msft_0-1617699142176.png

For the related .pbix file, see pls Attachment.

Saludos
Kelly

Did I answer your question? Mark my position as a solution!

same date occurring multiple times need to display expected hours single time.pbix
Message 37 of 38
1,098 Views
0
Syndicate_Admin
Administrator
Administrator

‎04-06-2021 03:22 AM

Don't @gladies123,

First create an index column;

Next, create 4 columns as follows:

Time = 
var _search=SEARCH("(",'Table'[jira entry date],1,0)
Return
MID('Table'[jira entry date],_search+1,6)
Date = 
var _search=SEARCH("(",'Table'[jira entry date],1,0)
Return
LEFT('Table'[jira entry date],_search-1)
_Hour = LEFT('Table'[Actual Hours],SEARCH("hr",'Table'[Actual Hours],1,0)-1)
Expected Hours = 
var _latertime=CALCULATE(MAX('Table'[Time]),FILTER('Table','Table'[Index]>EARLIER('Table'[Index])&&'Table'[Date]=EARLIER('Table'[Date])))
Return
IF(DATEDIFF('Table'[Time],_latertime,HOUR)<=3 && 'Table'[Index]=MINX(FILTER('Table','Table'[Date]=EARLIER('Table'[Date])),'Table'[Index]),CALCULATE(SUM('Table'[_Hour]),FILTER('Table','Table'[Index]>=EARLIER('Table'[Index])&&'Table'[Date]=EARLIER('Table'[Date]))),BLANK())

And you'll see:

v-kelly-msft_0-1617699142176.png

For the related .pbix file, see pls Attachment.

Saludos
Kelly

Did I answer your question? Mark my position as a solution!

same date occurring multiple times need to display expected hours single time.pbix
Message 36 of 38
1,098 Views
0
Syndicate_Admin
Administrator
Administrator

‎04-06-2021 03:19 AM

Don't @gladies123,

First create an index column;

Next, create 4 columns as follows:

Time = 
var _search=SEARCH("(",'Table'[jira entry date],1,0)
Return
MID('Table'[jira entry date],_search+1,6)
Date = 
var _search=SEARCH("(",'Table'[jira entry date],1,0)
Return
LEFT('Table'[jira entry date],_search-1)
_Hour = LEFT('Table'[Actual Hours],SEARCH("hr",'Table'[Actual Hours],1,0)-1)
Expected Hours = 
var _latertime=CALCULATE(MAX('Table'[Time]),FILTER('Table','Table'[Index]>EARLIER('Table'[Index])&&'Table'[Date]=EARLIER('Table'[Date])))
Return
IF(DATEDIFF('Table'[Time],_latertime,HOUR)<=3 && 'Table'[Index]=MINX(FILTER('Table','Table'[Date]=EARLIER('Table'[Date])),'Table'[Index]),CALCULATE(SUM('Table'[_Hour]),FILTER('Table','Table'[Index]>=EARLIER('Table'[Index])&&'Table'[Date]=EARLIER('Table'[Date]))),BLANK())

And you'll see:

v-kelly-msft_0-1617699142176.png

For the related .pbix file, see pls Attachment.

Saludos
Kelly

Did I answer your question? Mark my position as a solution!

same date occurring multiple times need to display expected hours single time.pbix
Message 35 of 38
1,098 Views
0
Syndicate_Admin
Administrator
Administrator

‎04-06-2021 03:16 AM

Don't @gladies123,

First create an index column;

Next, create 4 columns as follows:

Time = 
var _search=SEARCH("(",'Table'[jira entry date],1,0)
Return
MID('Table'[jira entry date],_search+1,6)
Date = 
var _search=SEARCH("(",'Table'[jira entry date],1,0)
Return
LEFT('Table'[jira entry date],_search-1)
_Hour = LEFT('Table'[Actual Hours],SEARCH("hr",'Table'[Actual Hours],1,0)-1)
Expected Hours = 
var _latertime=CALCULATE(MAX('Table'[Time]),FILTER('Table','Table'[Index]>EARLIER('Table'[Index])&&'Table'[Date]=EARLIER('Table'[Date])))
Return
IF(DATEDIFF('Table'[Time],_latertime,HOUR)<=3 && 'Table'[Index]=MINX(FILTER('Table','Table'[Date]=EARLIER('Table'[Date])),'Table'[Index]),CALCULATE(SUM('Table'[_Hour]),FILTER('Table','Table'[Index]>=EARLIER('Table'[Index])&&'Table'[Date]=EARLIER('Table'[Date]))),BLANK())

And you'll see:

v-kelly-msft_0-1617699142176.png

For the related .pbix file, see pls Attachment.

Saludos
Kelly

Did I answer your question? Mark my position as a solution!

same date occurring multiple times need to display expected hours single time.pbix
Message 34 of 38
1,098 Views
0
Syndicate_Admin
Administrator
Administrator

‎04-06-2021 03:13 AM

Don't @gladies123,

First create an index column;

Next, create 4 columns as follows:

Time = 
var _search=SEARCH("(",'Table'[jira entry date],1,0)
Return
MID('Table'[jira entry date],_search+1,6)
Date = 
var _search=SEARCH("(",'Table'[jira entry date],1,0)
Return
LEFT('Table'[jira entry date],_search-1)
_Hour = LEFT('Table'[Actual Hours],SEARCH("hr",'Table'[Actual Hours],1,0)-1)
Expected Hours = 
var _latertime=CALCULATE(MAX('Table'[Time]),FILTER('Table','Table'[Index]>EARLIER('Table'[Index])&&'Table'[Date]=EARLIER('Table'[Date])))
Return
IF(DATEDIFF('Table'[Time],_latertime,HOUR)<=3 && 'Table'[Index]=MINX(FILTER('Table','Table'[Date]=EARLIER('Table'[Date])),'Table'[Index]),CALCULATE(SUM('Table'[_Hour]),FILTER('Table','Table'[Index]>=EARLIER('Table'[Index])&&'Table'[Date]=EARLIER('Table'[Date]))),BLANK())

And you'll see:

v-kelly-msft_0-1617699142176.png

For the related .pbix file, see pls Attachment.

Saludos
Kelly

Did I answer your question? Mark my position as a solution!

same date occurring multiple times need to display expected hours single time.pbix
Message 33 of 38
1,098 Views
0
Syndicate_Admin
Administrator
Administrator

‎04-06-2021 03:10 AM

Don't @gladies123,

First create an index column;

Next, create 4 columns as follows:

Time = 
var _search=SEARCH("(",'Table'[jira entry date],1,0)
Return
MID('Table'[jira entry date],_search+1,6)
Date = 
var _search=SEARCH("(",'Table'[jira entry date],1,0)
Return
LEFT('Table'[jira entry date],_search-1)
_Hour = LEFT('Table'[Actual Hours],SEARCH("hr",'Table'[Actual Hours],1,0)-1)
Expected Hours = 
var _latertime=CALCULATE(MAX('Table'[Time]),FILTER('Table','Table'[Index]>EARLIER('Table'[Index])&&'Table'[Date]=EARLIER('Table'[Date])))
Return
IF(DATEDIFF('Table'[Time],_latertime,HOUR)<=3 && 'Table'[Index]=MINX(FILTER('Table','Table'[Date]=EARLIER('Table'[Date])),'Table'[Index]),CALCULATE(SUM('Table'[_Hour]),FILTER('Table','Table'[Index]>=EARLIER('Table'[Index])&&'Table'[Date]=EARLIER('Table'[Date]))),BLANK())

And you'll see:

v-kelly-msft_0-1617699142176.png

For the related .pbix file, see pls Attachment.

Saludos
Kelly

Did I answer your question? Mark my position as a solution!

same date occurring multiple times need to display expected hours single time.pbix
Message 32 of 38
1,098 Views
0
Syndicate_Admin
Administrator
Administrator

‎04-06-2021 03:07 AM

Don't @gladies123,

First create an index column;

Next, create 4 columns as follows:

Time = 
var _search=SEARCH("(",'Table'[jira entry date],1,0)
Return
MID('Table'[jira entry date],_search+1,6)
Date = 
var _search=SEARCH("(",'Table'[jira entry date],1,0)
Return
LEFT('Table'[jira entry date],_search-1)
_Hour = LEFT('Table'[Actual Hours],SEARCH("hr",'Table'[Actual Hours],1,0)-1)
Expected Hours = 
var _latertime=CALCULATE(MAX('Table'[Time]),FILTER('Table','Table'[Index]>EARLIER('Table'[Index])&&'Table'[Date]=EARLIER('Table'[Date])))
Return
IF(DATEDIFF('Table'[Time],_latertime,HOUR)<=3 && 'Table'[Index]=MINX(FILTER('Table','Table'[Date]=EARLIER('Table'[Date])),'Table'[Index]),CALCULATE(SUM('Table'[_Hour]),FILTER('Table','Table'[Index]>=EARLIER('Table'[Index])&&'Table'[Date]=EARLIER('Table'[Date]))),BLANK())

And you'll see:

v-kelly-msft_0-1617699142176.png

For the related .pbix file, see pls Attachment.

Saludos
Kelly

Did I answer your question? Mark my position as a solution!

same date occurring multiple times need to display expected hours single time.pbix
Message 31 of 38
1,098 Views
0
Syndicate_Admin
Administrator
Administrator

‎04-06-2021 03:04 AM

Don't @gladies123,

First create an index column;

Next, create 4 columns as follows:

Time = 
var _search=SEARCH("(",'Table'[jira entry date],1,0)
Return
MID('Table'[jira entry date],_search+1,6)
Date = 
var _search=SEARCH("(",'Table'[jira entry date],1,0)
Return
LEFT('Table'[jira entry date],_search-1)
_Hour = LEFT('Table'[Actual Hours],SEARCH("hr",'Table'[Actual Hours],1,0)-1)
Expected Hours = 
var _latertime=CALCULATE(MAX('Table'[Time]),FILTER('Table','Table'[Index]>EARLIER('Table'[Index])&&'Table'[Date]=EARLIER('Table'[Date])))
Return
IF(DATEDIFF('Table'[Time],_latertime,HOUR)<=3 && 'Table'[Index]=MINX(FILTER('Table','Table'[Date]=EARLIER('Table'[Date])),'Table'[Index]),CALCULATE(SUM('Table'[_Hour]),FILTER('Table','Table'[Index]>=EARLIER('Table'[Index])&&'Table'[Date]=EARLIER('Table'[Date]))),BLANK())

And you'll see:

v-kelly-msft_0-1617699142176.png

For the related .pbix file, see pls Attachment.

Saludos
Kelly

Did I answer your question? Mark my position as a solution!

same date occurring multiple times need to display expected hours single time.pbix
Message 30 of 38
1,099 Views
0
Syndicate_Admin
Administrator
Administrator

‎04-06-2021 03:01 AM

Don't @gladies123,

First create an index column;

Next, create 4 columns as follows:

Time = 
var _search=SEARCH("(",'Table'[jira entry date],1,0)
Return
MID('Table'[jira entry date],_search+1,6)
Date = 
var _search=SEARCH("(",'Table'[jira entry date],1,0)
Return
LEFT('Table'[jira entry date],_search-1)
_Hour = LEFT('Table'[Actual Hours],SEARCH("hr",'Table'[Actual Hours],1,0)-1)
Expected Hours = 
var _latertime=CALCULATE(MAX('Table'[Time]),FILTER('Table','Table'[Index]>EARLIER('Table'[Index])&&'Table'[Date]=EARLIER('Table'[Date])))
Return
IF(DATEDIFF('Table'[Time],_latertime,HOUR)<=3 && 'Table'[Index]=MINX(FILTER('Table','Table'[Date]=EARLIER('Table'[Date])),'Table'[Index]),CALCULATE(SUM('Table'[_Hour]),FILTER('Table','Table'[Index]>=EARLIER('Table'[Index])&&'Table'[Date]=EARLIER('Table'[Date]))),BLANK())

And you'll see:

v-kelly-msft_0-1617699142176.png

For the related .pbix file, see pls Attachment.

Saludos
Kelly

Did I answer your question? Mark my position as a solution!

same date occurring multiple times need to display expected hours single time.pbix
Message 29 of 38
1,099 Views
0
Syndicate_Admin
Administrator
Administrator

‎04-06-2021 02:58 AM

Don't @gladies123,

First create an index column;

Next, create 4 columns as follows:

Time = 
var _search=SEARCH("(",'Table'[jira entry date],1,0)
Return
MID('Table'[jira entry date],_search+1,6)
Date = 
var _search=SEARCH("(",'Table'[jira entry date],1,0)
Return
LEFT('Table'[jira entry date],_search-1)
_Hour = LEFT('Table'[Actual Hours],SEARCH("hr",'Table'[Actual Hours],1,0)-1)
Expected Hours = 
var _latertime=CALCULATE(MAX('Table'[Time]),FILTER('Table','Table'[Index]>EARLIER('Table'[Index])&&'Table'[Date]=EARLIER('Table'[Date])))
Return
IF(DATEDIFF('Table'[Time],_latertime,HOUR)<=3 && 'Table'[Index]=MINX(FILTER('Table','Table'[Date]=EARLIER('Table'[Date])),'Table'[Index]),CALCULATE(SUM('Table'[_Hour]),FILTER('Table','Table'[Index]>=EARLIER('Table'[Index])&&'Table'[Date]=EARLIER('Table'[Date]))),BLANK())

And you'll see:

v-kelly-msft_0-1617699142176.png

For the related .pbix file, see pls Attachment.

Saludos
Kelly

Did I answer your question? Mark my position as a solution!

same date occurring multiple times need to display expected hours single time.pbix
Message 28 of 38
1,099 Views
0
Syndicate_Admin
Administrator
Administrator

‎04-06-2021 02:55 AM

Don't @gladies123,

First create an index column;

Next, create 4 columns as follows:

Time = 
var _search=SEARCH("(",'Table'[jira entry date],1,0)
Return
MID('Table'[jira entry date],_search+1,6)
Date = 
var _search=SEARCH("(",'Table'[jira entry date],1,0)
Return
LEFT('Table'[jira entry date],_search-1)
_Hour = LEFT('Table'[Actual Hours],SEARCH("hr",'Table'[Actual Hours],1,0)-1)
Expected Hours = 
var _latertime=CALCULATE(MAX('Table'[Time]),FILTER('Table','Table'[Index]>EARLIER('Table'[Index])&&'Table'[Date]=EARLIER('Table'[Date])))
Return
IF(DATEDIFF('Table'[Time],_latertime,HOUR)<=3 && 'Table'[Index]=MINX(FILTER('Table','Table'[Date]=EARLIER('Table'[Date])),'Table'[Index]),CALCULATE(SUM('Table'[_Hour]),FILTER('Table','Table'[Index]>=EARLIER('Table'[Index])&&'Table'[Date]=EARLIER('Table'[Date]))),BLANK())

And you'll see:

v-kelly-msft_0-1617699142176.png

For the related .pbix file, see pls Attachment.

Saludos
Kelly

Did I answer your question? Mark my position as a solution!

same date occurring multiple times need to display expected hours single time.pbix
Message 27 of 38
1,100 Views
0
Syndicate_Admin
Administrator
Administrator

‎04-06-2021 02:52 AM

Don't @gladies123,

First create an index column;

Next, create 4 columns as follows:

Time = 
var _search=SEARCH("(",'Table'[jira entry date],1,0)
Return
MID('Table'[jira entry date],_search+1,6)
Date = 
var _search=SEARCH("(",'Table'[jira entry date],1,0)
Return
LEFT('Table'[jira entry date],_search-1)
_Hour = LEFT('Table'[Actual Hours],SEARCH("hr",'Table'[Actual Hours],1,0)-1)
Expected Hours = 
var _latertime=CALCULATE(MAX('Table'[Time]),FILTER('Table','Table'[Index]>EARLIER('Table'[Index])&&'Table'[Date]=EARLIER('Table'[Date])))
Return
IF(DATEDIFF('Table'[Time],_latertime,HOUR)<=3 && 'Table'[Index]=MINX(FILTER('Table','Table'[Date]=EARLIER('Table'[Date])),'Table'[Index]),CALCULATE(SUM('Table'[_Hour]),FILTER('Table','Table'[Index]>=EARLIER('Table'[Index])&&'Table'[Date]=EARLIER('Table'[Date]))),BLANK())

And you'll see:

v-kelly-msft_0-1617699142176.png

For the related .pbix file, see pls Attachment.

Saludos
Kelly

Did I answer your question? Mark my position as a solution!

same date occurring multiple times need to display expected hours single time.pbix
Message 26 of 38
1,100 Views
0
Syndicate_Admin
Administrator
Administrator

‎04-06-2021 02:49 AM

Don't @gladies123,

First create an index column;

Next, create 4 columns as follows:

Time = 
var _search=SEARCH("(",'Table'[jira entry date],1,0)
Return
MID('Table'[jira entry date],_search+1,6)
Date = 
var _search=SEARCH("(",'Table'[jira entry date],1,0)
Return
LEFT('Table'[jira entry date],_search-1)
_Hour = LEFT('Table'[Actual Hours],SEARCH("hr",'Table'[Actual Hours],1,0)-1)
Expected Hours = 
var _latertime=CALCULATE(MAX('Table'[Time]),FILTER('Table','Table'[Index]>EARLIER('Table'[Index])&&'Table'[Date]=EARLIER('Table'[Date])))
Return
IF(DATEDIFF('Table'[Time],_latertime,HOUR)<=3 && 'Table'[Index]=MINX(FILTER('Table','Table'[Date]=EARLIER('Table'[Date])),'Table'[Index]),CALCULATE(SUM('Table'[_Hour]),FILTER('Table','Table'[Index]>=EARLIER('Table'[Index])&&'Table'[Date]=EARLIER('Table'[Date]))),BLANK())

And you'll see:

v-kelly-msft_0-1617699142176.png

For the related .pbix file, see pls Attachment.

Saludos
Kelly

Did I answer your question? Mark my position as a solution!

same date occurring multiple times need to display expected hours single time.pbix
Message 25 of 38
1,101 Views
0
Syndicate_Admin
Administrator
Administrator

‎04-06-2021 02:47 AM

Don't @gladies123,

First create an index column;

Next, create 4 columns as follows:

Time = 
var _search=SEARCH("(",'Table'[jira entry date],1,0)
Return
MID('Table'[jira entry date],_search+1,6)
Date = 
var _search=SEARCH("(",'Table'[jira entry date],1,0)
Return
LEFT('Table'[jira entry date],_search-1)
_Hour = LEFT('Table'[Actual Hours],SEARCH("hr",'Table'[Actual Hours],1,0)-1)
Expected Hours = 
var _latertime=CALCULATE(MAX('Table'[Time]),FILTER('Table','Table'[Index]>EARLIER('Table'[Index])&&'Table'[Date]=EARLIER('Table'[Date])))
Return
IF(DATEDIFF('Table'[Time],_latertime,HOUR)<=3 && 'Table'[Index]=MINX(FILTER('Table','Table'[Date]=EARLIER('Table'[Date])),'Table'[Index]),CALCULATE(SUM('Table'[_Hour]),FILTER('Table','Table'[Index]>=EARLIER('Table'[Index])&&'Table'[Date]=EARLIER('Table'[Date]))),BLANK())

And you'll see:

v-kelly-msft_0-1617699142176.png

For the related .pbix file, see pls Attachment.

Saludos
Kelly

Did I answer your question? Mark my position as a solution!

same date occurring multiple times need to display expected hours single time.pbix
Message 24 of 38
1,101 Views
0
Syndicate_Admin
Administrator
Administrator

‎04-06-2021 02:44 AM

Don't @gladies123,

First create an index column;

Next, create 4 columns as follows:

Time = 
var _search=SEARCH("(",'Table'[jira entry date],1,0)
Return
MID('Table'[jira entry date],_search+1,6)
Date = 
var _search=SEARCH("(",'Table'[jira entry date],1,0)
Return
LEFT('Table'[jira entry date],_search-1)
_Hour = LEFT('Table'[Actual Hours],SEARCH("hr",'Table'[Actual Hours],1,0)-1)
Expected Hours = 
var _latertime=CALCULATE(MAX('Table'[Time]),FILTER('Table','Table'[Index]>EARLIER('Table'[Index])&&'Table'[Date]=EARLIER('Table'[Date])))
Return
IF(DATEDIFF('Table'[Time],_latertime,HOUR)<=3 && 'Table'[Index]=MINX(FILTER('Table','Table'[Date]=EARLIER('Table'[Date])),'Table'[Index]),CALCULATE(SUM('Table'[_Hour]),FILTER('Table','Table'[Index]>=EARLIER('Table'[Index])&&'Table'[Date]=EARLIER('Table'[Date]))),BLANK())

And you'll see:

v-kelly-msft_0-1617699142176.png

For the related .pbix file, see pls Attachment.

Saludos
Kelly

Did I answer your question? Mark my position as a solution!

same date occurring multiple times need to display expected hours single time.pbix
Message 23 of 38
1,101 Views
0
Syndicate_Admin
Administrator
Administrator

‎04-06-2021 02:42 AM

Don't @gladies123,

First create an index column;

Next, create 4 columns as follows:

Time = 
var _search=SEARCH("(",'Table'[jira entry date],1,0)
Return
MID('Table'[jira entry date],_search+1,6)
Date = 
var _search=SEARCH("(",'Table'[jira entry date],1,0)
Return
LEFT('Table'[jira entry date],_search-1)
_Hour = LEFT('Table'[Actual Hours],SEARCH("hr",'Table'[Actual Hours],1,0)-1)
Expected Hours = 
var _latertime=CALCULATE(MAX('Table'[Time]),FILTER('Table','Table'[Index]>EARLIER('Table'[Index])&&'Table'[Date]=EARLIER('Table'[Date])))
Return
IF(DATEDIFF('Table'[Time],_latertime,HOUR)<=3 && 'Table'[Index]=MINX(FILTER('Table','Table'[Date]=EARLIER('Table'[Date])),'Table'[Index]),CALCULATE(SUM('Table'[_Hour]),FILTER('Table','Table'[Index]>=EARLIER('Table'[Index])&&'Table'[Date]=EARLIER('Table'[Date]))),BLANK())

And you'll see:

v-kelly-msft_0-1617699142176.png

For the related .pbix file, see pls Attachment.

Saludos
Kelly

Did I answer your question? Mark my position as a solution!

same date occurring multiple times need to display expected hours single time.pbix
Message 22 of 38
1,101 Views
0
Syndicate_Admin
Administrator
Administrator

‎04-06-2021 02:39 AM

Don't @gladies123,

First create an index column;

Next, create 4 columns as follows:

Time = 
var _search=SEARCH("(",'Table'[jira entry date],1,0)
Return
MID('Table'[jira entry date],_search+1,6)
Date = 
var _search=SEARCH("(",'Table'[jira entry date],1,0)
Return
LEFT('Table'[jira entry date],_search-1)
_Hour = LEFT('Table'[Actual Hours],SEARCH("hr",'Table'[Actual Hours],1,0)-1)
Expected Hours = 
var _latertime=CALCULATE(MAX('Table'[Time]),FILTER('Table','Table'[Index]>EARLIER('Table'[Index])&&'Table'[Date]=EARLIER('Table'[Date])))
Return
IF(DATEDIFF('Table'[Time],_latertime,HOUR)<=3 && 'Table'[Index]=MINX(FILTER('Table','Table'[Date]=EARLIER('Table'[Date])),'Table'[Index]),CALCULATE(SUM('Table'[_Hour]),FILTER('Table','Table'[Index]>=EARLIER('Table'[Index])&&'Table'[Date]=EARLIER('Table'[Date]))),BLANK())

And you'll see:

v-kelly-msft_0-1617699142176.png

For the related .pbix file, see pls Attachment.

Saludos
Kelly

Did I answer your question? Mark my position as a solution!

same date occurring multiple times need to display expected hours single time.pbix
Message 21 of 38
1,102 Views
0
Syndicate_Admin
Administrator
Administrator

‎04-06-2021 02:37 AM

Don't @gladies123,

First create an index column;

Next, create 4 columns as follows:

Time = 
var _search=SEARCH("(",'Table'[jira entry date],1,0)
Return
MID('Table'[jira entry date],_search+1,6)
Date = 
var _search=SEARCH("(",'Table'[jira entry date],1,0)
Return
LEFT('Table'[jira entry date],_search-1)
_Hour = LEFT('Table'[Actual Hours],SEARCH("hr",'Table'[Actual Hours],1,0)-1)
Expected Hours = 
var _latertime=CALCULATE(MAX('Table'[Time]),FILTER('Table','Table'[Index]>EARLIER('Table'[Index])&&'Table'[Date]=EARLIER('Table'[Date])))
Return
IF(DATEDIFF('Table'[Time],_latertime,HOUR)<=3 && 'Table'[Index]=MINX(FILTER('Table','Table'[Date]=EARLIER('Table'[Date])),'Table'[Index]),CALCULATE(SUM('Table'[_Hour]),FILTER('Table','Table'[Index]>=EARLIER('Table'[Index])&&'Table'[Date]=EARLIER('Table'[Date]))),BLANK())

And you'll see:

v-kelly-msft_0-1617699142176.png

For the related .pbix file, see pls Attachment.

Saludos
Kelly

Did I answer your question? Mark my position as a solution!

same date occurring multiple times need to display expected hours single time.pbix
Message 20 of 38
1,102 Views
0
Syndicate_Admin
Administrator
Administrator

‎04-06-2021 02:34 AM

Don't @gladies123,

First create an index column;

Next, create 4 columns as follows:

Time = 
var _search=SEARCH("(",'Table'[jira entry date],1,0)
Return
MID('Table'[jira entry date],_search+1,6)
Date = 
var _search=SEARCH("(",'Table'[jira entry date],1,0)
Return
LEFT('Table'[jira entry date],_search-1)
_Hour = LEFT('Table'[Actual Hours],SEARCH("hr",'Table'[Actual Hours],1,0)-1)
Expected Hours = 
var _latertime=CALCULATE(MAX('Table'[Time]),FILTER('Table','Table'[Index]>EARLIER('Table'[Index])&&'Table'[Date]=EARLIER('Table'[Date])))
Return
IF(DATEDIFF('Table'[Time],_latertime,HOUR)<=3 && 'Table'[Index]=MINX(FILTER('Table','Table'[Date]=EARLIER('Table'[Date])),'Table'[Index]),CALCULATE(SUM('Table'[_Hour]),FILTER('Table','Table'[Index]>=EARLIER('Table'[Index])&&'Table'[Date]=EARLIER('Table'[Date]))),BLANK())

And you'll see:

v-kelly-msft_0-1617699142176.png

For the related .pbix file, see pls Attachment.

Saludos
Kelly

Did I answer your question? Mark my position as a solution!

same date occurring multiple times need to display expected hours single time.pbix
Message 19 of 38
1,102 Views
0

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

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

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

View All
Top Kudoed Authors
View All