Extract Rate of item on Calendar Date

shivani_singhal — Fri, 07 Sep 2018 05:41:41 GMT

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

Re: Extract Rate of item on Calendar Date

santhiqlik — Fri, 07 Sep 2018 06:08:23 GMT

Hi Shivani,

I would handle this scenario as below.

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.
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'
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