Percentage Of Time Period

[MickBrowning]

Hello,

I am wondering how i could add a measure column to a table to show percentage complete of a time period.

So COMPLETE in the table below, if the current time was 15-NOV-2019 13:00, would look like this -

START	END	COMPLETE
14-NOV-2019 19:00	15-NOV-2019 07:00	100%
15-NOV-2019 07:00	15-NOV-2019 19:00	50%
15-NOV-2019 19:00	16-NOV-2019 07:00	0%

Basically, i need to show 100% for completed shifts, 0% for future shifts, and a % value of how far through the current shift we are...

In Excel i use this, which works great -

=IF(END<NOW(),1,IF(START>NOW(),0,1-((END-NOW())/(END-START))))

Any help would be appreciated!

M

[Anonymous]

@MickBrowning please try this and check if it is working as expected. 

Measure = 
VAR _start = MAX(Test[START])
VAR _end = MAX(Test[END])
VAR _now = NOW()
VAR diff1 = DATEDIFF(_now,_end,HOUR)
VAR diff2 = DATEDIFF(_start,_end,HOUR)
RETURN IF(diff1>0,DIVIDE((diff2-diff1),diff2,0),1)

[MickBrowning · Accepted solution]

Thanks @Anonymous , a slight tweak and its working!

The original returned a negative value for future dates, i need a zero.

Measure = 
VAR _start = MAX([START])
VAR _end = MAX([END])
VAR _now = NOW()
VAR diff1 = DATEDIFF(_now,_end,HOUR)
VAR diff2 = DATEDIFF(_start,_now, HOUR)
VAR diff3 = DATEDIFF(_start,_end,HOUR)
RETURN IF(diff1>0,IF(diff2>0,DIVIDE((diff3-diff1),diff3,0),0),1)