Extract Rate of item on Calendar Date

[shivani_singhal]

Hi,

My scenario is, I have to extract rate of products on each date of calendar. And I have only From Date, To Date, Product Code, and Rate.

In this I have two cases,

Case 1: Old Rate of Product is in between From Date and To Date.

Case 2: Rate of Product on current date is (date greater than From Date and To Date is '01-01-1900')

I am unable to handle this situation.

Kindly Help me on the same.

Thanks in advance.

Regards,

Shivani Singhal

[santhiqlik]

Hi Shivani,

I would handle this scenario as below.

1. I will generate data for all the dates between from date and to date. If the from date and to date are yearly wise, you can generate one date for each month which is month start date. Otherwise you can just generate all the dates between from and to date.
2. Before implementing the above step. First we need to change the '01-01-1900'  to today’s date. And you can create a flag like _CurrentRate which is set to 1 if the end date is '01-01-1900' 
3. After this your fact table will have Product code, Rate, Calendar Date, From date (optional), To Date (Optional).

Below syntax generates dates monthly wise

Load
*,
AddMonths(MonthStart([Start Date]),iterno()-1) as Date,

Resident MainData

while AddMonths(MonthStart([Start Date]),IterNo()-1) <= MonthStart([End Date])

Let me know if this helps !!

--Priya