Extracting MM/YYYY from current date & using in a calc

[skearton]

hi,

 

We have a drop down value list text field that sales reps can select the month / year they believe a job will be completed on and can be billed in. It looks like this: ...10/2014, 11/2014, 12/2014, 01/2015, 02/2015...etc. We call it the "Estimate Billing Date".

 

I have made a cross tab report with the sales rep's names in the rows. The columns are like this:

Previous Months (to catch anything possible behind on being billed), Current Month (label to be dynamic and actually state the current MM/YYYY , Next Month...etc for up to 4 months out.

 

I need to be able to create a calculation that can take the value in the "Estimate Billing Date" drop down list and evaluate it against the current date, to see what the difference is in months and then set the total of that invoice in the correct month, otherwise it sets the value to empty. I have created 5 different fields that hold the invoice value depending on the result of the calculation: PreviousMonths, CurrentMonth, NextMonth, 2MonthsOut, 3MonthsOut, 4MonthsOut.

 

I can then add up all of an individual's sales reps potential income over the next several months as well as add up all the upcoming months income.

 

I did have something working when we were only concerned about just the billing month (not worrying about the year) but of course we are close to crossing over to a new year now, so have to revisit this and I'm baffled.

[comment]

 I have created 5 different fields that hold the invoice value depending on the result of the calculation: PreviousMonths, CurrentMonth, NextMonth, 2MonthsOut, 3MonthsOut, 4MonthsOut.

 

I would suggest using a single repeating calculation field with 5 repetitions instead. The calculation could be something like =

Let ( [
estimate = Extend ( Estimate Billing Date ) ;
estDate = Date ( Left ( estimate ; 2 ) ; 1 ; Right ( estimate ; 4 ) ) ;
today = Get ( CurrentDate ) ;
repDate = Date ( Month ( today ) + Get ( CalculationRepetitionNumber ) - 2 ; 1 ; Year ( today ) )
] ;
Case ( estDate = repDate ; Extend ( Amount ) )
)

Note that you can also use a single summary field to total the individual repetitions .

[skearton]

Thanks comment! That worked great.

How could I adjust the same formula but make the dynamic MM/YYYY labels for the columns?

 

I did try to swap out the "Amount" value in the case statement portion to be the "Estimate Billing Date" but that is not working.

[comment]

How could I adjust the same formula but make the dynamic MM/YYYY labels for the columns?

 

Just use this part:

Let ( [
today = Get ( CurrentDate ) 
] ;
Date ( Month ( today ) + Get ( CalculationRepetitionNumber ) - 2 ; 1 ; Year ( today ) )
)

Set the result type to Date and format the field to display only the month and the year.

 

In case it wasn't clear, both calculations must be unstored.