Forum Discussion

AllanBerces's avatar
AllanBerces
Icon for Post Prodigy rankPost Prodigy
1 year ago
Solved

Difference value from previous day to recent date

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
Need Help
  • danextian's avatar
    danextian
    1 year ago

    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's avatar
    v-pnaroju-msft
    1 year ago

    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.

     

     

rohit1991's avatar
rohit1991
Icon for Super User rankSuper User
1 year ago

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