Recent 4 fiscal weeks excluding this week

[markgraham123]

Hi all,

I have a master calendar, where i load fiscal week and fiscal date.

I want to load a new dimension where it contains the recent 4 fiscal weeks but not this week.

Can someone pls help me on this.

current fiscal week statement i'm using.

if(week(SoldDate) > 5,week(SoldDate)-5, week(SoldDate) + 47) as [Fiscal Week]
         

[sunny_talwar · Accepted solution]

Not sure if I understand completely, but may be this:

If(Match(Week(SoldDate), Week(Today())-1, Week(Today())-2, Week(Today())-3, Week(Today())-4), Week(SoldDate)) as [Fiscal Week]

[markgraham123]

Thanq Sunny...

Welcome back bro.

[sunny_talwar]

No problem Mark

I am glad that I am able to help you.

Best,

Sunny

[markgraham123]

Sunny, i have a small question.

The last weeks were showing for both 2014 and 2015. We need it only for 2015.

I tried giving the condition Max(year) and '2015' in If condition but its just loading nothing.

[sunny_talwar]

May be this:

If(Match(Week(SoldDate), Week(Today())-1, Week(Today())-2, Week(Today())-3, Week(Today())-4) and Year(SoldDate) = Year(Today()), Week(SoldDate)) as [Fiscal Week]

[markgraham123]

Hi Sunny,

It worked.

I tried in the other way, which too worked:

If([SoldDate]>= (Today()-28) and  [SoldDate] < (Today()),Week([SoldDate])) as [Last 4 Weeks]

Thank you again buddy