The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
Hi
My Oracle database is giving me the cumulative value for the production from a production line, I want to calculate the hourly values as I have done in the column "Hourly Values (I HAVE CALCULATED)" in excel
Each Shift ID has 12 hours and the value is the cumulative shift total, so hour 1 actual production should be the value given 16980 and hour 2 should be Hour 2 value - hour 1 value 31700-16980=14720
How to do this in powerBi
LINE_DESCR | SHIFT_ID | SHIFT_HOUR | VALUE | Hourly Values (I HAVE CALCULATED) |
Line 40 | 12572 | 1 | 16980 | 16980 |
Line 40 | 12572 | 2 | 31700 | 14720 |
Line 40 | 12572 | 3 | 44530 | 12830 |
Line 40 | 12572 | 4 | 60320 | 15790 |
Line 40 | 12572 | 5 | 76780 | 16460 |
Line 40 | 12572 | 6 | 89030 | 12250 |
Line 40 | 12572 | 7 | 97720 | 8690 |
Line 40 | 12572 | 8 | 105210 | 7490 |
Line 40 | 12572 | 9 | 119650 | 14440 |
Line 40 | 12572 | 10 | 136460 | 16810 |
Line 40 | 12572 | 11 | 153760 | 17300 |
Line 40 | 12572 | 12 | 170030 | 16270 |
Line 40 | 12573 | 1 | 17280 | 17280 |
Line 40 | 12573 | 2 | 34540 | 17260 |
Line 40 | 12573 | 3 | 50220 | 15680 |
Line 40 | 12573 | 4 | 65660 | 15440 |
Line 40 | 12573 | 5 | 82160 | 16500 |
Line 40 | 12573 | 6 | 98900 | 16740 |
Line 40 | 12573 | 7 | 114930 | 16030 |
Line 40 | 12573 | 8 | 131040 | 16110 |
Line 40 | 12573 | 9 | 148800 | 17760 |
Line 40 | 12573 | 10 | 164310 | 15510 |
Line 40 | 12573 | 11 | 182130 | 17820 |
Line 40 | 12573 | 12 | 199510 | 17380 |
Line 40 | 12574 | 1 | 16760 | 16760 |
Line 40 | 12574 | 2 | 33300 | 16540 |
Line 40 | 12574 | 3 | 47660 | 14360 |
Line 40 | 12574 | 4 | 64510 | 16850 |
Line 40 | 12574 | 5 | 78240 | 13730 |
Solved! Go to Solution.
Yes...I just had to add in a filter on Tag Name also. Thank you.
See my article on Mean Time Between Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/3395...
Also, https://community.powerbi.com/t5/Quick-Measures-Gallery/Periodic-Revenue-Reverse-YTD/m-p/373185#M111
I would go with the first one, just grab your current shift, subtract one, look that value up accounting for Shift Id, should be fairly simple.
Column =
VAR __CurrentShift = [SHIFT_HOUR]
VAR __CurrentValue = [VALUE]
VAR __PreviousValue =
MAXX(
FILTER(
'Table',
[LINE_DESCR] = EARLIER([LINE_DESCR]) &&
[SHIFT_ID] = EARLIER([SHIFT_ID]) &&
[SHIFT_HOUR] = __CurrentShift - 1
),
[VALUE]
)
RETURN
__CurrentValue - __PreviousValue
Yes...I just had to add in a filter on Tag Name also. Thank you.
User | Count |
---|---|
12 | |
8 | |
6 | |
6 | |
6 |
User | Count |
---|---|
24 | |
14 | |
13 | |
9 | |
7 |