468,107 Members | 1,347 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,107 developers. It's quick & easy.

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 3836
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

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

4 posts views Thread by Hans Gruber | last post: by
4 posts views Thread by Richard Hollenbeck | last post: by
3 posts views Thread by Paul Mendez | last post: by
2 posts views Thread by bufbec | last post: by
1 post views Thread by Solo | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.