Like for Like Increases / Filtered Table Comparisons / Start and End Values

[TCC]

Hi,

I have one table for valuaions ('Valuations') that looks like the following:

ValID	Property	ValDate	Valuation
1	1	31/12/2017	1,000,000
2	2	31/12/2017	1,500,000
3	3	31/12/2017	2,000,000
4	1	31/12/2018	1,000,000
5	2	31/12/2018	1,500,000
6	3	31/12/2018	2,000,000
7	1	31/12/2019	1,500,000
8	2	31/12/2019	1,750,000
9	4	31/12/2019	2,500,000

In my report I have applied a slicer linked to a related 'Calendar' table to filter the visuals on my report by date.

I would like to create a measure that determines the opening value at the ealiest date of the slicer of those assets still held at the latest date of the slicer.

E.g. If the date range on the slicer is 31/12/2017 - 31/12/2019 I would like the measure to return 2,500,000 (the sum of the values for properties 1 & 2 which are the only ones which have valuations on 31/12/2019 that also had valuations on 31/12/2017.

I have tried creating a VAR of the filtered 'Valuations' table at each date, lookups and various other ways of "checking the start value of the properties which are still held at the latest date".

Any help would be much appreciated.

Thanks

[stevedep · Accepted solution]

Hi,

I think you are looking for this:

SumOfMultiple = 
var v_min = MIN('Date'[Date])
var v_max = MAX('Date'[Date])
var tbl = FILTER(ALL('Table');'Table'[ValDate]= v_min || 'Table'[ValDate]= v_max) // both have entries on min or max date
var tbl_s = SUMMARIZE(tbl; 'Table'[Property];  "c"; COUNT('Table'[ValDate]); "firstval"; CALCULATE(SUM('Table'[Valuation]);'Table'[ValDate]=v_min)) //count entries for property
var ct = CALCULATETABLE(FILTER(tbl_s;[c]>1))

return
SUMX(ct;[firstval])

Giving:

Link to file here.

Hope this helps. Please mark as solution if this is what you were looking for.

Kind regards, Steve.