Solved: Create a measure that counts values where a condit... - Page 2

Anonymous · ‎11-09-2019

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 · ‎11-10-2019

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")

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

Proud to be a Super User!

View solution in original post

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

Helpful resources

Microsoft Fabric Learn Together

Power BI Monthly Update - April 2024

Fabric Community Update - April 2024