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.

     

     

amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
2 years ago

ChrisM32 , This seems like inventory calculation needs to build from start using date table

 

example

 

Inventory / OnHand
[Intial Inventory] + CALCULATE(SUM(Table[Ordered]),filter(date,date[date] <=maxx(date,date[date]))) - CALCULATE(SUM(Table[Sold]),filter(date,date[date] <=maxx(date,date[date])))

 

Inventory / OnHand
CALCULATE(firstnonblankvalue('Date'[Month]),sum(Table[Intial Inventory]),all('Date')) + CALCULATE(SUM(Table[Ordered]),filter(date,date[date] <=maxx(date,date[date]))) - CALCULATE(SUM(Table[Sold]),filter(date,date[date] <=maxx(date,date[date])))

 

 

Power BI Inventory On Hand: https://youtu.be/nKbJ9Cpb-Aw