topic Re: Cumulative.......break down shift cumulative to hourly values in DAX Commands and Tips

Cumulative.......break down shift cumulative to hourly values

moshea4 — Mon, 16 Mar 2020 14:36:41 GMT

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

Re: Cumulative.......break down shift cumulative to hourly values

Greg_Deckler — Mon, 16 Mar 2020 14:51:03 GMT

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/339586

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

Re: Cumulative.......break down shift cumulative to hourly values

moshea4 — Mon, 16 Mar 2020 15:25:12 GMT

Yes...I just had to add in a filter on Tag Name also. Thank you.