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