3 Replies Latest reply on Sep 17, 2014 8:40 AM by Shawn Wallwork

I have been posting on this question and not been able to get exactly the answer I need.  It's partly my fault for not including all of the information for what I want in my view, but I've included it in the attached workbook this time to give an idea of what I need.

I am comparing data that my boss usually looks at week-to-date, month-to-date, and year-to-date.  The only problem is when I have data for the most current week, say 3 days worth since today is Wednesday, it will compare it to the same fiscal week last year, except that last year's data for the fiscal week includes all 7 days in the week.

I had to create a field for fiscal year instead of using YEAR(Date) because our fiscal years don't fit entirely into one calendar year.  The crosstab in my attached workbook is exactly the final view that I need, only I want it to compare equivalent # of days for the most current week

Thanks for any help.

-Dan

Does your week start on Sunday or Monday?

--Shawn

Sunday.  So if today is Wednesday, we'll have Sunday, Monday, and Tuesday's data to view

So your question is about current week-to-date, which implies using TODAY() as the basis for the calculations. But your sample workbook has only data in January, so it's not of much use. Instead I used the Superstore data to workout the calculations for you. (See attached.) Here are the calcs:

Last Year Start of Week

DATEADD('week', -52, DATETRUNC('week', TODAY() ) )

Days in Current Week

DATEDIFF('day', DATETRUNC('week',TODAY() ), TODAY() )

Last Year WTD

DATEADD('day', [Days in Current Week], [Last Year Start of Week] )

Last Year Sales WTD

IF [Last Year Start of Week]<=[Order Date]

AND [Last Year WTD]>[Order Date] THEN [Sales]

END

Cheers,

--Shawn