Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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)
@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)
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
47 | |
22 | |
20 | |
15 | |
13 |
User | Count |
---|---|
49 | |
41 | |
39 | |
19 | |
19 |