I need to be able to work out Estimated Finishing Time using LastNonBlank (or something similar)

[billhawley]

To give some context...

We have 4 separate Tote reports that come through Excel every 30mins.

Each new report is identical to the last one except an added row of data that is the latest tote volume against time, all the times for the day are already included.

e.g.

Time         No. of TotesBelt1    Belt 2          Belt 3       Belt 4

13:00:00 | 400                       | 20           | 100           | 9 

13:30:00 | 395                       | 33           | 10             | 15
14:00:00 | 407                       | 12           | 45             | 120

14:30:00 |                              |                |                  |

etc...

What we would like to work out is our estiamted finishing time for the day.

The formula is:

TotalLatestTotes / 700 + CurrentTime = Estimated Finishing Time

584 / 700 = 0.83

0.83 is 49 minutes

16:43 + 49 minutes is 17:32 (Estimated Finishing Time)

I have tried using LASTNONBLANK but this is only pulling the highest number from each column and not the actual last non blank.

Is there a way around that so that it always uses the latest numbers coming in?

Thanks

[billhawley · Accepted solution]

Solved in another post: 
Solved: Re: Trying to work out estimated finishing times f... - Microsoft Power BI Community

[v-yangliu-msft]

Hi  @billhawley ,

Here are the steps you can follow：

1. Create measure.

all_measure =
var _now=NOW()
var _1=MID(_now,10,12)
var _time1=MID(_now,11,1)
var _time2=MID(_now,13,2)
var _time3=MID(_now,16,2)
var _nowtime=TIME(_time1+12,_time2,_time3)
return
MAXX(FILTER(ALL('Table'),'Table'[No]<>BLANK()&& 'Table'[Time]<=_nowtime),[Time])

Sum_No =
CALCULATE(SUM('Table'[No]),FILTER(ALL('Table'),
    'Table'[Time]= [all_measure]))

2. Result:

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[billhawley]

Hi Liu Yang,

This doesn't seem to be displaying anything as it is saying there is an integer value problem.

It is ok though as I have been able to use a work around for the time being.

Thanks anyway

[amitchandak]

@billhawley , The value you shared in the example I do see in the table 

You need like a measure like

example =

LASTNONBLANVALUE(Table[Time], Sum(Table[Belt1]) )

or

LASTNONBLANVALUE(Table[Time], Sum(Table[Belt1])+ Sum(Table[Belt2])+Sum(Table[Belt3])+Sum(Table[Belt4]) )

[billhawley]

@amitchandak 

Thanks, that sort of works but...

I don't need a SUM of the columns as we are monitoring throughput not totals.

So at 13:30 I would expect to see 395 and then at 14:00 - 407.

I just need the latest number from the table depending on the time of day.

Thanks