cancel
Showing results for 
Search instead for 
Did you mean: 
Reply

Next date limited by another 'filter table' with dates

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...

3 REPLIES 3
Nathaniel_C Super Contributor
Super Contributor

Re: Next day based on another dates table

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 day based on another dates table

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.

Highlighted

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

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

Helpful resources

Announcements
New Kudos Received Badges Coming

New Kudos Received Badges Coming

Kudos to you if you earned one of these! Check your inbox for a notification.

Microsoft Implementation for Communities Wins Award

Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Kudoed Authors (Last 30 Days)
Users online (1,423)