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

     

     

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.