Direct query and calculated column based on two tables

Replying to myself ;)

Looks like I've found the way to make it work.

This formula seems to be giving me what I need (although the syntax is very messy in my point of view - compared to vba or c).

Tried to add some parenthesis to make it look cleaner, but then the formula just doesn't work....

if [Production_Order_Status]<>"TECO" then if Date.Month([COOIS_Commit]) = Date.Month(DateTime.LocalNow()) then "IN" else "OUT" else if [Production_Order_Status]="TECO" then if Date.Month([Shipping_Date]) = Date.Month(DateTime.LocalNow()) then "IN" else "OUT" else "OUT"

An alternative would be:

if [Production_Order_Status]<>"TECO" and Date.IsInCurrentMonth([COOIS_Commit]) or

   [Production_Order_Status]="TECO" and Date.IsInCurrentMonth([Shipping_Date])

then "IN"

else "OUT"

However, it seems that either solution breaks Query Folding / Direct Query.

I'll take a further look at this aspect.

Edit: no success as I'm trying with an Access database that gives me all dates in date/time format.

Maybe I was wrong and Direct Query did still work for you?

If not, then the following might be helpful for you:

It looks like Direct Query allows for calculation of the dates for beginning and the end of month:

Date.StartOfMonth(DateTime.Date(DateTime.LocalNow()))

Date.EndOfMonth(DateTime.Date(DateTime.LocalNow()))

Then you can try and add these as custom columns and use these for your formula.

If this all doesn't make sense then unfortunately my attempt to be helpful failed miserably in this case ... :smileyfrustrated: