Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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:
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:
I found a solution in Power Query, but still interested in how this would go in DAX...
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)
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.
User | Count |
---|---|
41 | |
27 | |
23 | |
19 | |
16 |
User | Count |
---|---|
55 | |
35 | |
21 | |
18 | |
15 |