How to modify this WTD Transformation?

[newbiemstr]

Report has fallowing objects on it
Store ……….attribute,
Revenue, WTD Rev, MTD Rev, YTD Rev…..Metrics
Yesterday, last week, Choose month, Trailing four weeks (filters)……Prompt

I am calculating WTD, MTD, and YTD using transformation tables each has two colums (ex:day and wtd_day_date) . Base fact table has data at day level. I am able to get the right data when I select Yesterday filter from prompt. But I am getting same data for all the metrics when I select any other filters.

Sample sql when I select Yesterday filter which is right:

select a11.StoreId STORE_ID,
sum((a11.[Sale Dollars] + a11.[Return Dollars])) WJXBFS1
into #ZZMD00
from vw_metric a11
where a11.DATE_ID = CONVERT(datetime, '2008-08-18 00:00:00', 120)
group by a11.StoreId

select a11.StoreId STORE_ID,
sum((a11.[Sale Dollars] + a11.[Return Dollars])) WJXBFS1
into #ZZMD01
from vw_metric a11
join Date_WTD a12
on (a11.DATE_ID = a12.WTD_DATE_ID)
where a12.DATE_ID = CONVERT(datetime, '2008-08-18 00:00:00', 120)
group by a11.StoreId

Sample sql when I select Lase week filter(doing the same for all metrics):

select a11.StoreId STORE_ID,
sum((a11.[Sale Dollars] + a11.[Return Dollars])) WJXBFS1
into #ZZMD00
from vw_metric a11
join L_DATE a12
on (a11.DATE_ID = a12.DATE_ID)
where a12.Week_ID = CONVERT(datetime, '2008-08-16 00:00:00', 120)
group by a11.StoreId

select a11.StoreId STORE_ID,
sum((a11.[Sale Dollars] + a11.[Return Dollars])) WJXBFS1
into #ZZMD01
from vw_metric a11
join L_DATE a12
on (a11.DATE_ID = a12.DATE_ID)
where a12.Week_ID = CONVERT(datetime, '2008-08-16 00:00:00', 120)
group by a11.StoreId

Can some please help me solve this metric.
Thanks in advance.

 

[reisw]

Your problem is probably because your transformation has the relationship defined at the day level only and you are filtering on week. Try adding day/date dimensionality to your metric with ending lookup and it should use the last day of the week when writing the query for your metric.