Calculating Dates for Fiscal Months

[RZ15]

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

[Scott Price]

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

[RZ15]

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.