0

I need to calculate a YTD value each day based on a budget table. My table looks like this:

Month     Rev-50100   Rev-50101
1/31/19   75000.00    364.27
2/28/19   76000.00    360.57
3/31/19   82000.00    391.58

I'm able to handle the MTD pretty easily with a "PerDay" column that takes the monthly value and divides by the number of days in that month, then multiply the PerDay value based on the day of the month.

For YTD, I would need to add each previous month, then a MTD value for the current month. So on March 2nd my expected outcome is (75000+76000+ ((82000/31)*2)) = 156,290.32

I have been trying to use a combination of RunningValue and my MTD calculation, but RunningValue is adding in the entire current month, and not letting me get YTD value.

Marc
  • 87
  • 2
  • 9

1 Answers1

1

If I'm understanding correctly, I may have a solution for you. You need each month from the Rev-50100 column, except the last value and add the MTD value instead.

To solve this, you can use the Last function to remove the last value from the RunningValue and then add MTD.

=RunningValue(Fields!Rev-50100.Value, Sum, Nothing) 
 - Last(Fields!Rev-50100.Value, Nothing) 
 + ReportItems!MtdTextbox.Value
Steve-o169
  • 2,066
  • 1
  • 12
  • 21