Forum Discussion

nates05's avatar
nates05
Frequent Visitor
6 years ago
Solved

Running Total Difference between each Time Period

Hi, my data is below.   I generate data that is a running total of items sold ("Total Sold") per item and wanted to generate values that measures the difference between periods ("Sold per period")....
Need Help
  • v-eachen-msft's avatar
    v-eachen-msft
    6 years ago

    Hi nates05 ,

     

    You can try my method:
    At first, create a new column "rank".

    rank =
RANKX ( 'Table', 'Table'[Period],, ASC, DENSE )

    Then create your goal column.

    Sold per period =
VAR a =
    CALCULATE (
        FIRSTNONBLANK ( 'Table'[Total Sold], 1 ),
        FILTER (
            'Table',
            'Table'[Serial Codes] = EARLIER ( 'Table'[Serial Codes] )
                && 'Table'[rank]
                    = EARLIER ( 'Table'[rank] ) - 1
        )
    )
RETURN
    'Table'[Total Sold] - a

    Here is the result.

     

    Best Regards,

    Eads

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

v-eachen-msft's avatar
v-eachen-msft
Community Support
6 years ago

Hi nates05 ,

 

You can try my method:
At first, create a new column "rank".

rank =
RANKX ( 'Table', 'Table'[Period],, ASC, DENSE )

Then create your goal column.

Sold per period =
VAR a =
    CALCULATE (
        FIRSTNONBLANK ( 'Table'[Total Sold], 1 ),
        FILTER (
            'Table',
            'Table'[Serial Codes] = EARLIER ( 'Table'[Serial Codes] )
                && 'Table'[rank]
                    = EARLIER ( 'Table'[rank] ) - 1
        )
    )
RETURN
    'Table'[Total Sold] - a

Here is the result.

 

Best Regards,

Eads

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

  • nates05's avatar
    nates05
    Frequent Visitor
    6 years ago

    Hi, would this have an M code equivalent?

     

    I realized I need to insert these columns in power query itself.

     

    Thanks!