Calculating Targets for Monthly

[#1]

Hi All

I have a sales table for different branches and a targets for each branch

targets are weekly for each branch but the Sales is by Monthly

how can i convert the weekly target to monthly

i need to calculate the sales percentage against the target 

Please Suggest

below is my Master Calendar

Sales:

LOAD Branch,

     Date,

     Sales

FROM

\\Target Cal.xls]

(biff, embedded labels, table is [Sales$]);

Temp: 

Load 

               min(Date) as minDate, 

               max(Date) as maxDate 

Resident Sales;

Let vMinDate = Num(Peek('minDate', 0, 'Temp')); 

Let vMaxDate = Num(Peek('maxDate', 0, 'Temp')); 

DROP Table Temp; 

TempCalendar: 

LOAD 

               $(vMinDate) + Iterno()-1 As Num, 

               Date($(vMinDate) + IterNo() - 1) as TempDate 

               AutoGenerate 1 While $(vMinDate) + IterNo() -1 <= $(vMaxDate); 

MasterCalendar:

Load 

               TempDate AS Date,

               NUM(TempDate) AS NumDate,

               Week(TempDate) as Week,

               Year(TempDate) As Year, 

               Month(TempDate) As Month, 

               MonthName(TempDate) As MonthYear, 

               Day(TempDate) As Day,

               YeartoDate(TempDate)*-1 as CurYTDFlag, 

               YeartoDate(TempDate,-1)*-1 as LastYTDFlag, 

               If(InMonthToDate(TempDate, '$(vToday)'-1,0),1,0) as CurMTDFlag,              

               If(InMonthToDate(TempDate, ADDMONTHS('$(vToday)'-1,-1),0),1,0) as LastMTDFlag, 

               If(InMonthToDate(TempDate, ADDMONTHS('$(vToday)'-1,-2),0),1,0) as PrevMTDFlag, 

               WeekDay(TempDate) as DEC_WeekDay        

Resident TempCalendar 

Order By TempDate ASC; 

Drop Table TempCalendar

[Gysbert_Wassenaar]

Since weeks can start in one month and end in the next it's not always possible to map a week to a month. You will have to decide first how you intend to handle that issue.

Regarding the mixed granularity of the periods... perhaps this blog post helps: Fact Table with Mixed Granularity‌

[#3]

HI Gysbert   Thank You

is there a way i can do it by weekly calculate the sales percentage against the target

[swuehl]

In your excel file, it looks like your sales is recorded on a daily basis. So it should be easy to compare sales and target on a weekly basis. If you want a monthly comparison, you could consider breaking down your targets to daily values, too. Then your facts are of same grain and you can compare on any time scale you like.