Difference value from previous day to recent date

Hi AllanBerces 

Could you please follow these steps below:

1. Load your data into Power BI with columns Job, Progress Date, Hours.

2. Create a calculated column to capture the previous date for each job:

Prev Date =
VAR d = Data[Progress Dt]
VAR j = Data[Job]
RETURN
   CALCULATE(
      MAX(Data[Progress Dt]),
      FILTER(ALL(Data), Data[Job] = j && Data[Progress Dt] < d)
   )

3.Create another column for the previous hours:

Prev Hours =
VAR j = Data[Job]
VAR p = Data[Prev Date]
RETURN
   IF(
       ISBLANK(p), BLANK(),
      CALCULATE(MAX(Data[Hours]),
          FILTER(ALL(Data), Data[Job] = j && Data[Progress Dt] = p)
       )
   )

4. Subtract to get the difference:

Diff vs Prev =
IF(ISBLANK(Data[Prev Hours]), BLANK(), Data[Hours] - Data[Prev Hours])

5. To make sure you only show the final revision per job, add a flag:

Is Latest =
VAR j = Data[Job]
VAR latest = CALCULATE(MAX(Data[Progress Dt]), FILTER(ALL(Data), Data[Job] = j))
RETURN IF(Data[Progress Dt] = latest, 1, 0)

In your table visual, apply a filter where Is Latest = 1. The column Diff vs Prev will then give you the correct “Revision Hours” (latest hours previous hours).

If you want day-to-day difference (column):

Previous Hours =
VAR CurrentJob = Table1[Job]
VAR CurrentDate = Table1[Progress Date]
RETURN
CALCULATE (
MAX ( Table1[Hours] ),
FILTER ( Table1,
Table1[Job] = CurrentJob &&
Table1[Progress Date] < CurrentDate
)
)

Hours Difference = Table1[Hours] - Table1[Previous Hours]

If you only need latest vs previous latest (measure):

Hours Change =
VAR MaxDate = MAX ( Table1[Progress Date] )
VAR PrevDate =
CALCULATE (
MAX ( Table1[Progress Date] ),
FILTER ( Table1, Table1[Job] = MAX ( Table1[Job]) &&
Table1[Progress Date] < MaxDate )
)
VAR MaxHours = CALCULATE ( MAX ( Table1[Hours] ), Table1[Progress Date] = MaxDate )
VAR PrevHours = CALCULATE ( MAX ( Table1[Hours] ), Table1[Progress Date] = PrevDate )
RETURN MaxHours - PrevHours

Hi Allan,

You can create a table with the three fields Job, Progress Date and Hours, each as column, no measure. Then you can create a visual calculation (click on "..." on the visual) and create following calculation:

Revision Hrs= if( next([Job])<>[Job], [Hours] - PREVIOUS([Hours]),0)

 

You get following:

 

AllanBerces 

Diff Hours =
VAR MaxDate =
CALCULATE ( MAX ( 'Table'[Progress Date] ), ALL ( 'Table' ) )
VAR PrevDate =
CALCULATE (
MAX ( 'Table'[Progress Date] ),
FILTER ( ALL ( 'Table' ), 'Table'[Progress Date] < MaxDate )
)
VAR MaxHours =
CALCULATE ( SUM ( 'Table'[Hours] ), 'Table'[Progress Date] = MaxDate )
VAR PrevHours =
CALCULATE ( SUM ( 'Table'[Hours] ), 'Table'[Progress Date] = PrevDate )
RETURN
MaxHours - PrevHours