Forum Discussion
Helper IICheck between two dates and two tables
Hello,
Let's say that I have two tables:
Table 1
| A | Date |
|----|------------|
| A1 | 01/02/2012 |
| A1 | 03/04/2015 |
| A2 | 04/03/2012 |
| A5 | 25/07/2018 |
| A1 | 04/03/2012 |
| A2 | 05/06/2015 |
Table 2
| A | Date 1 | Date 2 |
|----|-----------------|------------|
| A1 | 01/01/2012 | 01/01/2013 |
| A1 | 01/01/2015 | 01/01/2016 |
| A2 | 01/01/2012 | 01/01/2014 |
| A5 | 01/01/2010 | 01/01/2010 |
I need to know how many elements from 'Table 1' are valid using the 'Table 2' as criterion. If the date indicated for an element on 'Table 1' is within a date for the same element in 'Table 2' we can consider that the element is valid (i.e. for the element A1 we have three lines in 'Table 1', two are valid because they are between 01/01/2013-01/01/2013 and the third one is also valid because is between 01/01/2015-01/01-2016).
I would like to obtain the result as follows:
| A | Date | Check |
|----|------------|---------|
| A1 | 01/02/2012 | 1 |
| A1 | 03/04/2015 | 1 |
| A2 | 04/03/2012 | 1 |
| A5 | 25/07/2018 | 0 |
| A1 | 04/03/2012 | 1 |
| A2 | 05/06/2015 | 0 |
I've already tried several ways with no success...
4 Replies
- Ahmedx
Super User
Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.
https://1drv.ms/u/s!AiUZ0Ws7G26RhnWZQGd_M2A7nHeE?e=H2yfK6 - Ahmedx
Of course you can write like this:
Column = VAR _tbl = SELECTCOLUMNS ( GENERATE ( 'Table2', GENERATESERIES ( 'Table2'[Date 1], 'Table2'[Date 2], 1 ) ), "@Category", [Category], "@Date", [Value] ) RETURN COUNTX( FILTER ( _tbl, [@Category] = [Category] && [@Date] = [Date] ), [@Date])+0 ------or---- Column = VAR _tbl = SELECTCOLUMNS ( GENERATE ( 'Table2', GENERATESERIES ( 'Table2'[Date 1], 'Table2'[Date 2], 1 ) ), "@Category", [Category], "@Date", [Value] ) RETURN COUNTROWS(FILTER ( _tbl, [@Category] = [Category] && [@Date] = [Date] ))+0 - Greg_Deckler
Community Champion
capko Here's another approach in addition to Ahmedx
Check = VAR __A = MAX('Table'[A]) VAR __Date = MAX('Table'[Date]) VAR __Table = ADDCOLUMNS( FILTER('Table2', [A] = __A), "__Check", IF(__Date >= [Date 1] && __Date <= [Date 2], 1, 0) ) VAR __Result = IF(SUMX(__Table, [__Check]) > 0, 1, 0) RETURN __Result - Ahmedx
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
