Forum Discussion
Two Calculated Columns with the same formulas are returning different results
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
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
| 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
But the Prior week works and filters to the current week
CurrentWeek= Lookupvalue(DateTable[ISPRIORWEEK],DateTable[WeekOfYearKey],'FactTable'[WeekOfYearKey])
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
-
2 Replies
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]
)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
-
