Next date limited by another 'filter table' with dates

[richard-powerbi]

Table1: ID, DateA

1, 2019-01-01
2, 2019-01-02
3, 2019-01-03
4, 2019-01-04
5, 2019-01-05
6, 2019-01-06

 Table 2 (non working days): DateB

2019-01-02
2019-01-05
2019-01-06

Table 1 (calculated column added): ID, DateA, DateC

1, 2019-01-01, 2019-01-03
2, 2019-01-02, 2019-01-03
3, 2019-01-03, 2019-01-04
4, 2019-01-04, 2019-01-07
5, 2019-01-05, 2019-01-07
6, 2019-01-06, 2019-01-07

I want to add a calculated column to Table 1.

The logical steps are:

• Date after date 2019-01-01 in Table 1 is 2019-01-02.
• Check if date 2019-01-02 is in Table 2.
• If date 2019-01-02 is in Table 2 then check if 2019-01-03 is in Table 2.
• Repeat until there is a 'date after' result which is not in Table 2.

Note: it is possible that the next date does not exist in Table 1, so I'm looking for the next date in general which does not exist in Table 2. That's why 2019-01-07 is a valid result.

I found a solution in Power Query:

https://community.powerbi.com/t5/Power-Query/Add-column-with-next-date-filtered-by-another-dates-tab...

[richard-powerbi]

I found a solution in Power Query, but still interested in how this would go in DAX...

[Nathaniel_C]

Hi @richard-powerbi ,

Not sure if your description is spot on, "In other words, if the date exists in Table 2, the next day that does not exist in Table 2 should be taken." If it does not exist in Table 2, use the day from table 1? Not sure why 1/1 gets a result of 1/1. 

In the meantime, I imagine it will be an if with a lookup.

Let me know about the logic

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel

check = LOOKUPVALUE('DATE B'[Column1],'DATE B'[Column1],'DATE A'[Column2],1)

[richard-powerbi]

Thanks @Nathaniel_C for thinking with me.

I'm sorry, so emberrasing! My result table was not correct. I guess I had a long day behind me. I updated my original post. Still looking for a solution.