How to get regular value given cumulative?

[Jbell314]

Basically I'm given a running cumulative total (I'll show example below) and i need to have the individual values figured out

Here's how data look:

Week end date (date format)	Cumulative Hours	**Individual hours (what is needed to calculated i just inputed what its supposed to be obviously but isn't given to me)
1/10/2021	1	1
1/17/2021	6	5
1/24/2021	8	2
1/31/2021	10	2

I know the formula would just be like ((1/17/2021) - (1/10/2021) =  hrs for 1/17/2021 but i don't know what formulas to use to select the individual rows)

**The 3rd column is not given and needs to be calculated in powerbi (something that is super easy in excel but i have found difficult in powerbi lol)

Much appreciated

[MFelix · Accepted solution]

Hi @Jbell314 ,

You have 3 different ways to achieve this:

1. Power Query
2. Calculated Column
3. Measure

1. Power Query:
2.  

• Add an index column
• Add a calculated colum with the following syntax:

if [Index] = 0 then [Cumulative] else 
[Cumulative] - 
 #"Added Index"[Cumulative]{[Index] -1}

The #"Added Index" part must have the name of the previous step before adding the custom column

      2. Calculated Column

Individual Hours Calculated column =
Hours[Cumulative]
    - CALCULATE (
        SUM ( Hours[Cumulative] ),
        FILTER ( ALL ( Hours ), Hours[Index] = EARLIER ( Hours[Index] ) - 1 )
    )

     3. Measure

Individual Hours measure =
SUM ( Hours[Cumulative] )
    - CALCULATE (
        SUM ( Hours[Cumulative] ),
        FILTER ( ALL ( Hours ), Hours[Index] = SELECTEDVALUE ( Hours[Index] ) - 1 )
    )

PBIX file attach.