Forum Discussion
uscutieda
6 years agoFrequent Visitor
Calculate Production Rate per hour
Hello all I need some help to calculate the rate per hours in a PBI table, using the column "Time" as a reference. This is what I have so far in PBI An this is what I'd like to have: (I bui...
- 6 years ago
hi uscutieda
The problem is that your [Date] column is a datetime format, and they have different time value in each day, so they are different value for each day.
You just need to add a date format column for [Date], and then use this date format column in the formula.
New Date = DATE(YEAR('AL2 CP TotalCount'[Date]),MONTH('AL2 CP TotalCount'[Date]),DAY('AL2 CP TotalCount'[Date]))Result = VAR _lastlinetime = CALCULATE( MAX('AL2 CP TotalCount'[Time]), FILTER( 'AL2 CP TotalCount', 'AL2 CP TotalCount'[Line] = EARLIER('AL2 CP TotalCount'[Line]) && 'AL2 CP TotalCount'[New Date] = EARLIER('AL2 CP TotalCount'[New Date]) && 'AL2 CP TotalCount'[Time] < EARLIER('AL2 CP TotalCount'[Time]) ) ) RETURN VAR _lastlinevalue = IF( ISBLANK(_lastlinetime), 0, CALCULATE( SUM('AL2 CP TotalCount'[Value]), FILTER( 'AL2 CP TotalCount', 'AL2 CP TotalCount'[Line] = EARLIER('AL2 CP TotalCount'[Line]) && 'AL2 CP TotalCount'[New Date] = EARLIER('AL2 CP TotalCount'[New Date]) && 'AL2 CP TotalCount'[Time] = _lastlinetime ) ) ) RETURN 'AL2 CP TotalCount'[Value] - _lastlinevalueand here is sample pbix file, please try it.
Regards,
Lin
Ashish_Mathur
Super User
6 years agoHi,
Assuming the numbers in the value column will always be in ascending order, try this calculated column formula
=[Value]-CALCULATE(MAX(Data[Value]),FILTER(Data,Data[Time]<EARLIER(Data[Time])&&Data[Line]=EARLIER(Data[Line]))
Hope this helps.