topic Re: Percentage Of Time Period in DAX Commands and Tips

Percentage Of Time Period

MickBrowning — Fri, 15 Nov 2019 15:17:02 GMT

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!

Re: Percentage Of Time Period

Anonymous — Fri, 15 Nov 2019 15:46:55 GMT

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)

Re: Percentage Of Time Period

MickBrowning — Fri, 15 Nov 2019 16:09:15 GMT

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)