Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
richard-powerbi
Post Patron
Post Patron

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
richard-powerbi
Post Patron
Post Patron

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

Nathaniel_C
Super User
Super User

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)




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors