Hello experts,
I'm having trouble figuring out how to do create a prior year columns for my existing WTD and MTD columns.
My calendar is a 454 Type with the fiscal year starting Feb 1 among some other weird specifics.
An example of my columns giving me a "WTD" identifier is as follows (please note I'm not an analyst, just a lowly accountant trying to build some measures so my existing columns may be completely inefficient).
| Year+Week+Day | This WTD | Beg.OfThisWeek | This WTD PY |
| 2019077 | | | |
| 2019076 | | | |
| 2019075 | | | |
| 2019074 | | | |
| 2019073 | Week to Date | | |
| 2019072 | Week to Date | | |
| 2019071 | Week to Date | Week Start | |
| 2019067 | | | |
| 2019066 | | | |
| 2019065 | | | |
| 2019064 | | | |
| 2019063 | | | |
| 2019062 | | | |
| 2019061 | | | |
The above table represents the scenario in which I'm 3 days into my 7th week of the current fiscal year (2019)
Formula's for each column is as follows:
Year+Week+Day = CONCATENATE(Calendar_Lookup[Year+Week#],Calendar_Lookup[DayOfWeekNumber])
This WTD = if(and(Calendar_Lookup[Year+Week+Day] <= LOOKUPVALUE(Calendar_Lookup[Year+Week+Day],Calendar_Lookup[Date],today()-1),
Calendar_Lookup[Year+Week+Day]>=LOOKUPVALUE(Calendar_Lookup[Year+Week+Day],Calendar_Lookup[Beg.OfThisWeek],"Week Start")),"Week To Date",Blank())
Beg.OfThisWeek = if(and(Calendar_Lookup[This Week]="This Week",Calendar_Lookup[DayOfWeekNumber]=1),"Week Start",blank())
I'm trying to create a "This WTD Prior Year" column which would essentially identify the rows which are exactly 1 year previous of the Year+Week+Day value of any rows that have a "Week To Date" value in the "This WTD" column.
What I would expect is that in my example above, the only rows which this formula would identify would be rows with Year+Week+Day values of 2018073, 2018072, 2018071 - i.e. the first 3 days of the 7th fiscal week of the prior fiscal year.
My attempt was as follows:
This WTD LY = if(Calendar_Lookup[Year+Week+Day]=LOOKUPVALUE(Calendar_Lookup[WM Year+Week+Day],Calendar_Lookup[This WTD],"Week To Date")-1000,"This WTD PY",BLANK()) however this give sme the error of "a table of multiple values was supplied where a single value was expected". I realize this is due to the use of the Lookupvalue function but I'm not sure how to get around it.
I'd appreciate some help on this!
Thanks!
