Two Calculated Columns with the same formulas are returning different results

[Anonymous]

Hello,

I have a date table set up with two calculated columns- One returns "Current Week" and the other returns "Prior Week"

Current week is defined as 

ISCURRENTWEEK = IF(WEEKNUM(TODAY()-7,1) = Datedmn[WeekOfYear] && Datedmn[Year] = YEAR(TODAY()),"Current Week","Not Current Week")

returns

DateKey	WeekOfYearKey	ISCURRENTWEEK	ISPRIORWEEK
4/15/2022	202216	Current Week	Not Current Week
4/14/2022	202216	Current Week	Not Current Week
4/13/2022	202216	Current Week	Not Current Week
4/12/2022	202216	Current Week	Not Current Week
4/11/2022	202216	Current Week	Not Current Week
4/10/2022	202216	Current Week	Not Current Week
4/16/2022	202216	Current Week	Not Current Week

ISPRIORWEEK is the same formula 

ISPRIORWEEK = IF(WEEKNUM(TODAY()-14,1) = Datedmn[WeekOfYear]&& Datedmn[Year] = YEAR(TODAY()),"Prior Week","Not Current Week")

Returns

DateKey	WeekOfYearKey	ISPRIORWEEK
4/3/2022	202215	Prior Week
4/8/2022	202215	Prior Week
4/7/2022	202215	Prior Week
4/6/2022	202215	Prior Week
4/5/2022	202215	Prior Week
4/4/2022	202215	Prior Week
4/9/2022	202215	Prior Week

These two formulas are in the same date table and are combined as new rows.

I have a fact table that is doing calculations by week, that has the WeekOfYear as a column. I am trying to do a simple lookupvalue to retun if its the current week or prior week or neither (Because if its neither, the value is "Not Current Week")

Current Week isn't working it's only returning blanks  

CurrentWeek= Lookupvalue(DateTable[ISCURRENTWEEK],DateTable[WeekOfYearKey],'FactTable'[WeekOfYearKey])

But the Prior week works and filters to the current week

CurrentWeek= Lookupvalue(DateTable[ISPRIORWEEK],DateTable[WeekOfYearKey],'FactTable'[WeekOfYearKey])

Any insight why the exact same formulas would cause one to return only blanks (and "Not Current Weeks" for some, but not all values?)

Thanks,

[Anonymous · Accepted solution]

Hi @Anonymous ,

• If there is a relationship between the result and search tables, in most cases, using RELATED function instead of LOOKUPVALUE is more efficient and provides better performance.

• The search_value and alternateResult parameters are evaluated before the function iterates through the rows of the search table.

• Avoid using ISERROR or IFERROR functions to capture an error returned by LOOKUPVALUE. If some inputs to the function will result in an error when a single output value cannot be determined, providing an alternateResult parameter is the most reliable and highest performing way to handle the error.

You could try below formula:

CurrentWeek = calculate(max(DateTable[ISCURRENTWEEK]),filter(DateTable,DateTable[WeekOfYearKey]=FactTable[WeekOfYearKey]))

Best Regards,

Jay

[amitchandak]

@Anonymous , try like

Week Type =
var _st = today() +-1*WEEKDAY(today() ,2)+1
var _end = today() + 7-1*WEEKDAY(today(),2)
return
Switch( True(),
[Date] >= _st && [Date] <= _end ,"This Week" ,
[Date] >= _st-7 && [Date] <= _end -7,"Last Week" ,
[Week Name]
)

or

Week Type =
var _st = today() +-1*WEEKDAY(today() ,1)+1
var _end = today() + 7-1*WEEKDAY(today(),1)
return
Switch( True(),
[Date] >= _st && [Date] <= _end ,"This Week" ,
[Date] >= _st-7 && [Date] <= _end -7,"Last Week" ,
[Week Name]
)