Dateadd and Lookupvalue With Blank Values at Random

[MFoster]

Hi,

I have a table with a few calculated columns that seemed to be working correctly but now there are several values being returned as blank. I have tried changing some of the negative values to positive and it will return a result for some dates but not others. Could this be an issue with memory? I’m running the X86 version of desktop due to some limitations of a OBDC connection.

Thanks for the Help!

BOM [Lot]

[v-jianboli-msft · Accepted solution]

Hi @MFoster ,

Here are some limitations of DATEADD function that may influence the result:

• The result table includes only dates that exist in the dates column.

• If the dates in the current context do not form a contiguous interval, the function returns an error.

Please try:

Week Of = [Date]-[Day of Week]

Week Of Production Report = 
var _a = IF([Day of Week]=0,-6,[Day of Week]-1)
return [Date]+_a

Output:

Your LOOKUPVALUE function seems to involve more than one table?

What do each of these tables look like and how do they relate to each other?

Please provide me with more details about your table and your problem or share me with your pbix file after removing sensitive data.

Refer to:

How to provide sample data in the Power BI Forum

How to Get Your Question Answered Quickly

Best Regards,

Jianbo Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[MFoster]

I was able to use power query to add the “Lot” column and eliminate the LOOKUPVALUE function.

Using the variable instead of the dateadd worked and everything is populating as expected.

Thanks for the help!