Forum Discussion

capko's avatar
capko
Helper II
3 years ago

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

 

Need Help

4 Replies

  • Ahmedx's avatar
    Ahmedx
    Super User
    3 years ago

    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's avatar
    Greg_Deckler
    Community Champion
    3 years ago

    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's avatar
    Ahmedx
    Super User
    3 years ago

    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.