Solved: Re: Reg: the same date that occurs multiple times ... - Page 2

gladies123 · ‎04-01-2021

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

Gladis

Syndicate_Admin · ‎04-06-2021

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:

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

Syndicate_Admin · ‎04-06-2021

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:

For the related .pbix file, see pls Attachment.

Saludos
Kelly

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

Syndicate_Admin · ‎04-06-2021

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:

For the related .pbix file, see pls Attachment.

Saludos
Kelly

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

Syndicate_Admin · ‎04-06-2021

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:

For the related .pbix file, see pls Attachment.

Saludos
Kelly

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

Syndicate_Admin · ‎04-06-2021

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:

For the related .pbix file, see pls Attachment.

Saludos
Kelly

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

Syndicate_Admin · ‎04-06-2021

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:

For the related .pbix file, see pls Attachment.

Saludos
Kelly

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

Syndicate_Admin · ‎04-06-2021

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:

For the related .pbix file, see pls Attachment.

Saludos
Kelly

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

Syndicate_Admin · ‎04-06-2021

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:

For the related .pbix file, see pls Attachment.

Saludos
Kelly

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

Syndicate_Admin · ‎04-06-2021

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:

For the related .pbix file, see pls Attachment.

Saludos
Kelly

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

Syndicate_Admin · ‎04-06-2021

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:

For the related .pbix file, see pls Attachment.

Saludos
Kelly

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

Syndicate_Admin · ‎04-06-2021

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:

For the related .pbix file, see pls Attachment.

Saludos
Kelly

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

Syndicate_Admin · ‎04-06-2021

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:

For the related .pbix file, see pls Attachment.

Saludos
Kelly

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

Syndicate_Admin · ‎04-06-2021

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:

For the related .pbix file, see pls Attachment.

Saludos
Kelly

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

Syndicate_Admin · ‎04-06-2021

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:

For the related .pbix file, see pls Attachment.

Saludos
Kelly

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

Syndicate_Admin · ‎04-06-2021

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:

For the related .pbix file, see pls Attachment.

Saludos
Kelly

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

Syndicate_Admin · ‎04-06-2021

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:

For the related .pbix file, see attachment pls.

Saludos
Kelly

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

v-kelly-msft · ‎04-06-2021

Hi @gladies123 ,

First create an index column;

Then create 4 columns as below:

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 will see:

For the related .pbix file,pls see attached.

Best Regards,
Kelly

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

PhilipTreacy · ‎04-01-2021

Hi @gladies123

The problem isn't quite clear. In your expected output your are still displaying the same datesmultipel times. So you want to only display Expected Hours once for each date.

Can you please supply your data and descibe exactly how you want the result to be created. How are you calculating Expected Hours?

Where are you displaying this data? In a PBI visual? Your screenshot shows Excel?

Regards

Phil

Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.

Blog :: YouTube Channel :: Connect on Linkedin

Proud to be a Super User!

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

Helpful resources

Fabric Community Update - July 2025

Power BI Monthly Update - July 2025

How to Get Your Question Answered Quickly

Party with Power BI’s own Guy in a Cube

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

Helpful resources

Fabric Community Update - July 2025

Power BI Monthly Update - July 2025

How to Get Your Question Answered Quickly