calculated column to find difference in value per day

[HDD328]

Hello,

I am having trouble creating a calculated column that will take the Hrs value from the current day and subtract from the previous day for all incoming data. (example data below)

I've tried multiple different ways, but can't seem to get the expected result. Any help is appreciated!

[BITomS]

Hi @HDD328 ,

I agree with @lkalawski that where possible, try to use measures. But for reference, this is how you could create a calculated column:

CalCol =
'Table'[Hrs]
- CALCULATE (
SUM ( 'Table'[Hrs] ),
ALL ( 'Table' ),
'Table'[UNIT] = EARLIER ( 'Table'[UNIT] ),
'Table'[DATE]
= EARLIER ( 'Table'[DATE].[Date] ) - 1
)

Hope this helps!

[lkalawski · Accepted solution]

Hi @HDD328 ,

I suggest doing this type of calculation using a measure, because it is more optimal.

I have assumed Sum Hrs as Current Date, but this can be modified depending on the context of the task:

Cur Day Hours = 
Sum('Table'[Hrs])

Just calculate the Hrs value for the previous day:

Prv Day Hours = 
VAR __Date = MAX('Table'[DATE])
VAR __Unit = SELECTEDVALUE('Table'[Unit])
VAR __PrevDay = __Date - 1
VAR PreviousHrs = 
    CALCULATE(
        MAX('Table'[Hrs]),
        FILTER(
            ALL('Table'),
            'Table'[Unit] = SELECTEDVALUE('Table'[Unit]) &&
            'Table'[DATE] = MAX('Table'[DATE]) - 1
        )
    )
RETURN PreviousHrs

and then calculate the difference between today and yesterday:

Diff = 
VAR __Result = [Cur Day Hours] - [Prv Day Hours]

RETURN IF(ISBLANK([Prv Day Hours]), BLANK(), __Result)

The result:

Resident Rockstar | Former Super User If I helped, please accept the solution and give kudos!  Connect with me Linkedin