Showing the latest available value based on the date field of another table.

[Revokez]

Hi, I have two seperate tables,

Table 1 is structured like this:

Table 1 Date	Value A
05/04/2024	500

Table 2 is structured like this:

Table 2 Date	Value B
01/04/2024	5
02/04/2024	3
03/04/2024	5
04/04/2025	4
05/04/2025	1

What i'd like to achieve is have a slicer for Table 2 that can filter to specific date ranges. e.g. between the 04/04/2025 -> 05/04/2025.

I'd like to then have a new column for Table 3 that was a calculaton of [Table 2.Value B (Summed)] / [Table 1.Value A (Latest)] 

So as an example from the tables above:

If i set the slicer to filter down to the date range of 04/05/2024 -> 05/05/2024 - the column calculation would be  (the sum of the values in the table 2 values) / (the value within the latest date in table 1). This would result in 5 / 500 = 0.01.

If the date range was 03/05/2024 -> 04/05/2024 there would be no corresponding latest date within Table 1 and so this column would return a blank value.

Any help would be appreciated! Thank you.

[Ashish_Mathur · Accepted solution]

Hi,

Create a Calendar Table.  Create a relationship (Many to One and Single) from the Date column of 2 tables to the Date column of the Calendar Table.  Create a Date slicer from the Calendar Table and select a range of dates.  Write these measures

Max selected date = max(Calendar[date])

Total = sum('Table 2'[Value B])

Latest = calculate(sum('Table 1'[Value A]),datesbetween(calendar[date],[max selected date],[max selected date]))

Ratio = divide([Total],[Latest])

Hope this helps.

[lbendlin]

Read about TREATAS.  It allows you to project filters across unrelated tables.