470,836 Members | 1,819 Online

# Calculating Dates for Fiscal Months

53
Hi guys,

I'm really drawing a blank here for how to deal with fiscal months in my monthly sales/receipts reports. My issue is that calculating the months is not as simple as saying 'if the invoice date is january then sum sales' because the first period may include the end of december or even the beginning of february and not all of january.

To help me with my problem, it's probably helpful to not think of it as fiscal months but think of it as fiscal periods (with there being 12 periods in a year). There are 3 periods in a quarter and each quarter has 13 weeks (52 weeks in a year divided by 4). The first period in each quarter is 5 weeks and the remaining 2 periods in each quarter are 4 weeks each.

Is there an expression where i can use Sum(IIF([InvoiceDate] Between ................... , [Sales],0) ? Or do I need to use a different approach?

Thanks in advance for any help,

Raza
May 26 '08 #1
2 4065
Scott Price
1,384 Expert 1GB
Expand|Select|Wrap|Line Numbers
1. DatePart("q", Now())
Will calculate the fiscal quarter of the given date (Now() in this case). However, there is no native function (that I know of) for calculating the week/month within the fiscal quarter. I would assume you will end up needing to code a function that will recognize which week/month of the year you are dealing with, and then assign it a fiscal week/month value.

Regards,
Scott
May 26 '08 #2
RZ15
53
Expand|Select|Wrap|Line Numbers
1. DatePart("q", Now())
Will calculate the fiscal quarter of the given date (Now() in this case). However, there is no native function (that I know of) for calculating the week/month within the fiscal quarter. I would assume you will end up needing to code a function that will recognize which week/month of the year you are dealing with, and then assign it a fiscal week/month value.

Regards,
Scott

Thanks for your input, I will try something but I need to turn my attention to something else for the time being. I will let you know if what I try works out when I do it.
May 27 '08 #3