Forum Discussion
How to get a value based on comparing dates from different tables
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 |
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&_1How 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 _ RongtieIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
4 Replies
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 _ RongtieIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
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?
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&_1How 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 _ RongtieIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi,
You should ideally be writing a calculated column formula in Table1. If you are OK with my approach, let me know.
