Dynamic Dashboard Dates

[#1]

Hi Community

I have a field "Budget Month", which takes the year, then the month from the 26th to the 25th of the following month e.g my current budget month is "2016 Jan - Feb" and before the 26th it was "2016 Dec - Jan" for dates that fall in Jan and "2015 Dec - Jan" for dates that fall in Dec. Now I would like to get the previous budget month. This must change dynamically as I make date selections and as time goes

Currently it only works correctly when I have a date selected and I would like it to work exactly as my current budget month. Can anyone please assist?

[jonathandienst · Accepted solution]

I suggest that you add something like the following fields to your master calendar:

MasterCalendar:    

LOAD

     CalDate As Date,

     ...         

     If(Day(CalDate) <= 25, Date(CalDate, 'YYYY MMM') & ' - ' & Date(AddMonths(CalDate, 1), 'MMM'),

          Date(AddMonths(CalDate, 1), 'YYYY MMM') & ' - ' & Date(AddMonths(CalDate, 2), 'MMM')) As Period,

     AutoNumber(If(Day(CalDate) <= 25, Date(CalDate, 'YYYY MMM') & ' - ' & Date(AddMonths(CalDate, 1), 'MMM'),

          Date(AddMonths(CalDate, 1), 'YYYY MMM') & ' - ' & Date(AddMonths(CalDate, 2), 'MMM'))) As PeriodSeq,

     ...

(Assumes you have a master calendar load. Adjust the script to change CalDate to the base date in your calendar)

Now you can find the previous period using =PeriodSeq - 1

If you don't have a master calendar, you can search this site for more information

[#3]

Thanks a lot Jonathan! This works perfectly