Forum Discussion

Jbell314's avatar
Jbell314
Regular Visitor
4 years ago
Solved

How to get regular value given cumulative?

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/202111
1/17/202165
1/24/202182
1/31/2021102

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

General Comment
Need Help
Tips and Tricks
Tutorial Requests
  • MFelix's avatar
    MFelix
    4 years ago

    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.

     

     

1 Reply

  • MFelix's avatar
    MFelix
    Super User
    4 years ago

    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.