How to calculate YTD that includes future data

[tweehh]

Hi VA experts,

I'm working on a report to compare the YTD revenue of this year to the previous year. I was able to calculate the YTD revenue from the beginning of the year to the current date. However, there is some future revenue that was captured in the data therefore I want to include revenue from the beginning of the year to end of the current month.  I could manually select the last day of the month for this year and last year. I was wondering there's a better way to do that without any manual effort.YTD (beginning of the year - end of current month)

Thank you for any help you can provide!

[SASKiwi]

One way to do this is to add month and year counters to your VA load table. Set it to 0 for the current month / year, -1 last month / year, -2 for the month / year before that and so on. This would be automatically created each time you update your VA load table.

Then a simple static filter to get current year would be: where year_counter = 0 and month_counter <= 0, and for last year: where year_counter = -1

[tweehh]

Thank you for your response! Can you provide more details on how to implement it in VA? Thanks!

[SASKiwi]

Here's how you can calculate a month and year counter based on a reporting date:

data want;
  set have;
  Reporting_Month = intck('MONTH', Reporting_Date, today()); 
  Reporting_Year  = intck('YEAR', Reporting_Date, today());
run; 

I'm assuming here that you can do this in "normal" SAS before you load into VA.

[acordes]

I've made the best experiences with a report_date variable in the data prep step via code.

You can use it easily in filters then. 

I'm not sure if this screenshot helps you out, but it shows well how the parallel period reacts to filters. 

good luck