Difference value from previous day to recent date

[AllanBerces]

Hi can anyone help me on how can i calcuate the difference of change between previous date to recent date.

The Max Progress date hours minus recent date hours.

RESULT

Thank you

[danextian · Accepted solution]

Try the following  calculated column

Revision Hours = 
VAR _job = 'Table'[Job]
VAR _tbl = 'Table'

-- Latest 2 dates across all jobs
VAR _latest2 =
    TOPN (
        2,
        VALUES ( 'Table'[Progress Date] ),
        'Table'[Progress Date], DESC
    )

-- Latest 2 dates for this specific job
VAR _latest2_job =
    TOPN (
        2,
        DISTINCT (
            SELECTCOLUMNS (
                FILTER ( 'Table', 'Table'[Job] = _job ),
                "Progress Date", 'Table'[Progress Date]
            )
        ),
        [Progress Date], DESC
    )

-- Global latest & previous
VAR _latestDate = MAXX ( _latest2, [Progress Date] )

-- Job-specific latest & previous
VAR _maxJobDate  = MAXX ( _latest2_job, [Progress Date] )
VAR _prevJobDate = MINX ( _latest2_job, [Progress Date] )

-- Hours for the latest date (job-scoped)
VAR _latestDateValue =
    MAXX (
        FILTER ( _tbl, [Progress Date] = _maxJobDate && [Job] = _job ),
        [Hours]
    )

-- Hours for the previous date (job-scoped)
VAR _prevDateValue =
    MAXX (
        FILTER ( _tbl, [Progress Date] = _prevJobDate && [Job] = _job ),
        [Hours]
    )

-- Difference
VAR _diff = _latestDateValue - _prevDateValue

-- Final result
VAR _result =
    SWITCH (
        TRUE(),
        'Table'[Progress Date] = _latestDate, _diff,
        'Table'[Progress Date] = _maxJobDate, 0
    )
RETURN
    _result

[v-pnaroju-msft · Accepted solution]

Thankyou, @Shahid12523, @OktayPamuk80, @rohit1991, @Kedar_Pande, and @danextian for your responses.

Hi AllanBerces,

We appreciate your enquiry via the Microsoft Fabric Community Forum.

Based on my understanding of the scenario, please find attached a screenshot and a sample PBIX file which may help to resolve the issue:

We hope the information provided assists in resolving the problem. Should you have any further queries, please feel free to contact the Microsoft Fabric Community.

Thank you.

 

[danextian · Accepted solution]

Try the following  calculated column

Revision Hours = 
VAR _job = 'Table'[Job]
VAR _tbl = 'Table'

-- Latest 2 dates across all jobs
VAR _latest2 =
    TOPN (
        2,
        VALUES ( 'Table'[Progress Date] ),
        'Table'[Progress Date], DESC
    )

-- Latest 2 dates for this specific job
VAR _latest2_job =
    TOPN (
        2,
        DISTINCT (
            SELECTCOLUMNS (
                FILTER ( 'Table', 'Table'[Job] = _job ),
                "Progress Date", 'Table'[Progress Date]
            )
        ),
        [Progress Date], DESC
    )

-- Global latest & previous
VAR _latestDate = MAXX ( _latest2, [Progress Date] )

-- Job-specific latest & previous
VAR _maxJobDate  = MAXX ( _latest2_job, [Progress Date] )
VAR _prevJobDate = MINX ( _latest2_job, [Progress Date] )

-- Hours for the latest date (job-scoped)
VAR _latestDateValue =
    MAXX (
        FILTER ( _tbl, [Progress Date] = _maxJobDate && [Job] = _job ),
        [Hours]
    )

-- Hours for the previous date (job-scoped)
VAR _prevDateValue =
    MAXX (
        FILTER ( _tbl, [Progress Date] = _prevJobDate && [Job] = _job ),
        [Hours]
    )

-- Difference
VAR _diff = _latestDateValue - _prevDateValue

-- Final result
VAR _result =
    SWITCH (
        TRUE(),
        'Table'[Progress Date] = _latestDate, _diff,
        'Table'[Progress Date] = _maxJobDate, 0
    )
RETURN
    _result

[AllanBerces]

Hi @danextian @v-pnaroju-msft @rohit1991 @Kedar_Pande @OktayPamuk80 thank you very much for the reply working perfectly.

[Kedar_Pande]

@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

💡 If this helped, please give Kudos 👍 or mark it as a Solution ✅.
Best regards,
Kedar
🌐 Connect on LinkedIn

[AllanBerces]

Hi @Shahid12523 @Kedar_Pande @rohit1991 @OktayPamuk80 sample like this, and also revision may happen several times. 

should not include on the calculation

to calculate Hours difference = 35

Thank you

[AllanBerces]

Hi @Shahid12523 @Kedar_Pande @rohit1991 @OktayPamuk80 thank you very much for the reply, but mistake on my side. calculate only the difference Hours if the Revision number change from Revision column. cos also happen even has change in hours but same revsion number. thus shall not be include on the calculation of Difference hours.

Thank you

[rohit1991]

Hi @AllanBerces 

To handle this, you can add one more helper column for the previous revision number:

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

Then update the difference calculation so it only applies when the revision changes:

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

This way you’ll only see the revision hours when there’s a true revision step, and any changes within the same revision number will be excluded.

[rohit1991]

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).

[OktayPamuk80]

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:

[Shahid12523]

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