topic Next date limited by another 'filter table' with dates in DAX Commands and Tips

Next date limited by another 'filter table' with dates

richard-powerbi — Wed, 02 Oct 2019 12:49:21 GMT

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-table/m-p/806700/highlight/false#M27054

Re: Next date limited by another 'filter table' with dates

Nathaniel_C — Mon, 30 Sep 2019 17:03:23 GMT

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)

Re: Next date limited by another 'filter table' with dates

richard-powerbi — Tue, 01 Oct 2019 07:17:14 GMT

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.

Re: Next date limited by another 'filter table' with dates

richard-powerbi — Wed, 02 Oct 2019 12:50:13 GMT

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