Forum Discussion
Percentage Of Time Period
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
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)
2 Replies
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)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)
