Forum Discussion

Frequent Visitor

8 years ago

Solved

Rolling 60 Day Balance

I am trying to do a 60 day rolling balance. Please review my screenshot below and help me see where I am going wrong.

Nothing is populating in the highlighted column.

60 Day Balance = CALCULATE(sum('BI - Discounts Balance'[Balance]),DATEADD('BI - Discounts Balance'[TransactionDate], 60, DAY))

Thank you !

Try this MEASURE

Rolling balance =
CALCULATE (
    SUM ( TableName[Amount] ),
    DATESINPERIOD ( TableName[Date], SELECTEDVALUE ( TableName[Date] ), -60, DAY )
)

sdjensen
Solution Sage
8 years ago
You can't do this kind of calculation as a column - add the calculation as a measure instead.
v-ljerr-msft
Microsoft Employee
8 years ago
Hi Data4Beer,

Have you tried creating a measure instead to see if it works? If you still have any question on this issue, feel free to post here. :smileyhappy:

Regards
- Data4Beer
  Frequent Visitor
  8 years ago
  
  Hi There
  
  Yes I have just tried it as a calculation. When I display the result as a table, the rolling 60 day balance just reflects the balance amount as shown in the screenshot below
  - Zubair_Muhammad
    Community Champion
    8 years ago
    HI Data4Beer
    
    Try this MEASURE
    
    Rolling balance = CALCULATE ( SUM ( TableName[Amount] ), DATESINPERIOD ( TableName[Date], SELECTEDVALUE ( TableName[Date] ), -60, DAY ) )