Create a measure that counts values where a condition complies between columns of 2 tables.

[Anonymous]

Hi all.

I have two tables A and B joined by an ID.

On each of the tables, there is a date column.

I'm trying to create a measure that counts the number of rows in which the date of table A is between the date in table B and the date in table B +1 day.

Table A

ID    DateA

1      2017-03-02

2      2018-06-03

3      2019-05-13

4      2017-08-23

Table B

ID    DateB

1      2017-03-01

2      2018-06-03

3      2014-05-28

4      2015-08-23

In this case, Id 1 and 2 have dates that comply with the condition, so the expected value of the measure should be 2.

Thanks in advance

[Nathaniel_C]

Hi @Anonymous ,
I believe it is 1 and 4 IDs that work.  The largest value in table B is  2018 6 3, so ID 2 3 in table A are over the limit for the upper value. The first measure works on a table, but to show it on a card it took a calculated column and the second measure.

Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel
 

A measure:

Between Dates = 
Var _curDate = MAX(TableA[Date])

var _calcMinDate = Calculate(MAX(TableB[Date]),ALLEXCEPT(TableB,TableB[Date]),TableB[Date]<_curDate)
var  _calcMaxDate = Calculate(Min(TableB[Date]),ALLEXCEPT(TableB,TableB[Date]),TableB[Date]>_curDate)

var _calc = CALCULATE([Countrows TA],FILTER(TableA,_curDate>_calcMinDate && _curDate<_calcMaxDate))
return

_calc

A Calculated Column:

Between = 
Var _curDate = (TableA[Date])

var _calcMinDate = Calculate(MAX(TableB[Date]),ALLEXCEPT(TableB,TableB[Date]),TableB[Date]<_curDate)
var  _calcMaxDate = Calculate(Min(TableB[Date]),ALLEXCEPT(TableB,TableB[Date]),TableB[Date]>_curDate)

var _calc = CALCULATE(MIN(TableA[ID]),FILTER(TableA,_curDate>_calcMinDate && _curDate<_calcMaxDate))
return

_calc

A measure to display the Calculated Column on a card.

Sum of rows = CALCULATE(COUNTROWS(TableA),TableA[Between]<>BLANK())

[Anonymous]

Hi @Nathaniel,

Thanks for the answer, probably I didn't explain my self properly.

You picked the min and max values on TableB and use them to filter all values on tableA.

What I want to do is apply a filter in row 1 of TableA depending of the value on TableB using the ID that is the field used to join both tables in the model.

Table A                        TableB

DateA               ID       DateB             

2017-03-02       1        2017-03-01   is DateA between DateB and DateB+1day?  YES

2018-06-03       2        2018-06-03   is DateA between DateB and DateB+1day? YES

2019-05-13       3        2014-05-28   is DateA between DateB and DateB+1day? NO

2017-08-23       4        2015-08-23   is DateA between DateB and DateB+1day? NO

So the result of the count of rows, in this case, should be 2 (for Id=1 and Id=2)

Thanks!

JAVI

[Nathaniel_C]

Hi @Anonymous 
2018-06-03       2        2018-06-03   is DateA between DateB and DateB+1day? YES

So which two date is DateA between on table B?

Nathaniel

[Anonymous]

Hi @Nathaniel_C 

Table A                        TableB

DateA               ID       DateB             

2017-03-02       1        2017-03-01   is DateA between DateB and DateB+1day?  YES

2017-03-02(DateA) is between 2017-03-01 (DateB) and 2017-03-02(DateB +1)

Between includes the values.

Javi

[Nathaniel_C]

Hi @Anonymous ,
So another way to say this is the date in table A for each ID must look at the same ID in table B, and be equal to the date in Table B, or Table B+1 for a yes. So it must match one of two dates only.
Nathaniel

[Anonymous]

Yes, that's more or less what I need but instead of "So it must match one of two dates only", the date A must be between the period.

In the example the condition is:

• DateA between DateB and DateB+1day

but it can also be:

• DateA between DateB and DateB+7day

So in this second case, the row must be counted is DateA is in any of the days od the period.

Thanks,

Javi

[Nathaniel_C · Accepted solution]

Hi @Anonymous ,
I believe this is what you are looking for in the outcome.
Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel



Calculated Column

Between =
VAR _curDate = ( TableA[Date] )
VAR _curID = TableA[ID]
VAR _curTableBDate =
    CALCULATE ( MAX ( TableB[Date] ), FILTER ( TableB, TableB[ID] = _curID ) )
VAR _maxPerDate = _curTableBDate + 1
RETURN
    IF ( _curDate >= _curTableBDate && _curDate <= _maxPerDate, "Yes", "No" )

Measure for table in picture.

Between Dates2 =
VAR _curDate =
    MAX ( TableA[Date] )
VAR _curID =
    MAX ( TableA[ID] )
VAR _curTableBDate =
    CALCULATE ( MAX ( TableB[Date] ), FILTER ( TableB, TableB[ID] = _curID ) )
VAR _maxPerDate = _curTableBDate + 1
RETURN
    IF ( _curDate >= _curTableBDate && _curDate <= _maxPerDate, "Yes", "No" )

Measure for Card in picture.

Sum of rows = CALCULATE(COUNTROWS(TableA),TableA[Between]="Yes")

[Anonymous]

Thanks a lot Nathaniel_C

That was exactly what I was looking for.

😀

[Nathaniel_C]

Hello, @Anonymous 
You are welcome! Glad we could get the logic straight.
You could create a variable and replace the 1 with it. Then it would be easy to change that at the beginning of the code to 7 or whatever.
Nathaniel

[Anonymous]

Hi Nathaniel, 

Thanks for the answer, probably I didn't explain myself properly.

You took the max and min date values of table B and counted all the values from table A in which the date is between those values.

What I want is to apply the condition on every row given that both tables are joined in the model by the ID.

i.e

Table A                              Table  B

DateA                 ID            DateB

2017-03-02         1             2017-03-01            is DateA between DateB and DateB+1day?   YES

2018-06-03         2             2018-06-03            is DateA between DateB and DateB+1day?   YES

2019-05-13         3             2014-05-28            is DateA between DateB and DateB+1day?   NO

2017-08-23         4             2015-08-23            is DateA between DateB and DateB+1day?   NO

So the measure should return a Value=2, that belongs to Ids 1 and 2.

I hope this way is better explained.

Thanks for helping anyway.