Forum Discussion

ChrisM32's avatar
ChrisM32
Frequent Visitor
2 years ago
Solved

Rolling Deductions

Hi all,   I'm trying to make a table visual like the below. I have a measure within the dataset that is calculating the sum of our deposits from an unrelated table. This table has no dates or other...
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi ChrisM32 ,

     

    If you want to calculate the rolling results, I think there should be a column like [DateTime]/[Index] in your table to make clear the temporal order of your data.

    If you data looks like as your sample, you can add an [Index] column in Power Query Editor.

    Calculated Column:

    Deposit Left = 
VAR _Total = CALCULATE(SUM('Table'[Deposit]),ALL('Table'))
VAR _ROLLING = CALCULATE(SUM('Table'[Deposit]),FILTER('Table','Table'[Index]<=EARLIER('Table'[Index])))
RETURN
_Total - _ROLLING

    Result is as below.

     

    Best Regards,
    Rico Zhou

     

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

     

     

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Hi ChrisM32 ,

 

If you want to calculate the rolling results, I think there should be a column like [DateTime]/[Index] in your table to make clear the temporal order of your data.

If you data looks like as your sample, you can add an [Index] column in Power Query Editor.

Calculated Column:

Deposit Left = 
VAR _Total = CALCULATE(SUM('Table'[Deposit]),ALL('Table'))
VAR _ROLLING = CALCULATE(SUM('Table'[Deposit]),FILTER('Table','Table'[Index]<=EARLIER('Table'[Index])))
RETURN
_Total - _ROLLING

Result is as below.

 

Best Regards,
Rico Zhou

 

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

 

 

  • ChrisM32's avatar
    ChrisM32
    Frequent Visitor
    2 years ago

    So strangely enough I created a table pretty much indentical to yours as I figured an index number would be the best way but I could not for the life of me figure out how to do the rolling calculation.

     

    You are aabsoloutely amazing, thank you!