How to get a value based on comparing dates from different tables

[toosie]

I have two tables where the relationship is many to many (the join is Account Number) and I am trying to create a measure doing the following:

From table1 - I want to get use the [TimeStamp] field and compare it to table 2 looking at two different date columns [CreatedOn] and [ClosedOn]

If [CreatedOn] <= [TimeStamp] <= [ClosedOn], I want to return the sum of [NetValue] from Table2. 

I have tried a few different formulas but cant seem to get it to read the dates properly - here is some sample data on the tables and what i am expecting for output:

Table 1:

Account	Timestamp	Overage
123	7/1/23	$3,000
123	5/10/23	$2,000
456	5/1/22	$5,000
789	6/8/23	$6,000

Table2

Account	CreatedOn	CloseOn	Value
123	6/18/23	12/30/23	$5,000
456	4/18/22	12/30/22	$2,000
456	6/12/23	12/30/23	$5,000
789	5/1/23	12/30/23	$2,000
789	6/1/23	12/30/23	$3,000

The measure should give this output:

Table 1:

Account	Timestamp	Overage	Measure (Value from Table2 where timestamp inbetween dates)
123	7/1/23	$3,000	$5,000
123	5/10/23	$2,000	0
456	5/1/22	$5,000	$2,000
789	6/8/23	$6,000	$5,000

[Ashish_Mathur]

Hi,

You should ideally be writing a calculated column formula in Table1.  If you are OK with my approach, let me know.

[v-rongtiep-msft]

Hi @toosie ,

I have created a simple sample, please refer to my pbix file to see if it helps you.

First remove the relationship between the tables.

Then create a measure.

Measure =
VAR _1 =
    CALCULATE (
        SUM ( Table2[Value] ),
        FILTER (
            ALL ( Table2 ),
            Table2[Account] = SELECTEDVALUE ( Table1[Account] )
                && Table2[CreatedOn] <= SELECTEDVALUE ( Table1[Timestamp] )
                && Table2[CloseOn] >= SELECTEDVALUE ( Table1[Timestamp] )
        )
    )
RETURN
    IF ( _1 <> BLANK (), _1, 0 )

How to Get Your Question Answered Quickly 

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards
Community Support Team _ Rongtie

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[toosie]

Hi Rongtie - this definitely got me closer to what was needed but when I attempt to use this measure in a line graph that shows it by Month/Year and no account details, the value doesnt work; it only seems to show in the table view - is there a way to get this?

Also, is there a way to get the value to update dynamically when filters are used on the whole page?

[v-rongtiep-msft · Accepted solution]

Hi @toosie ,

The reason why this measure can't be displayed on a monthly/yearly chart is because it is created based on a timestamp, which requires the year, month, and day to appear. You can try to have the timestamp appear on the x-axis instead of the month/year alone.

Or try to create a year/month column?

Column = var _year=YEAR(Table1[Timestamp])
var _month=MONTH(Table1[Timestamp])
var _1=IF(_month<10,0&_month,_month)
return _year&_1

How to get the value to update dynamically when filters are used on the whole page ?

You can enable the "Auto Refresh" feature in Power BI to update the value dynamically when filters are used on the whole page.

Automatic page refresh in Power BI Desktop - Power BI | Microsoft Learn

Best Regards
Community Support Team _ Rongtie

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.