Build a measure regarding reference date from and date to in a table

[DesMoZ]

Hi All,

I'm struggled with a dax approach, I've a table "RefValue":

Reference	Date From	Date To	Value
R1	01/01/2023	07/01/2023	100
R2	05/01/2023	10/01/2023	33
R3	15/12/2023	12/01/2023	25
R4	13/01/2023	15/01/2023	75

I would like to build a measure that will allow me to calculate a report like this : 

Reference	01/01	02/01	03/01	04/01	05/01	06/01	07/01	08/01	09/01	10/01	11/01	12/01	13/01	14/01	15/01
R1	100	100	100	100	100	100	100
R2					33	33	33	33	33	33
R3	25	25	25	25	25	25	25	25	25	25	25	25
R4													75	75	75
Total	125	125	125	125	158	158	158	58	58	58	25	25	75	75	75

And so for exemple if a period is selected in the "calendar table" through a slicer the measure [Total] will return the SUM of the value form this period.

So a kind of :

Total = CALCULATE ( Value, CalendarDate >= 'RefValue'[Date From] && CalendarDate <= 'RefValue'[Date To]

Thanks for your help.

[DesMoZ]

Many Thanks @FreemanZ it works perfectly

[FreemanZ · Accepted solution]

hi @DesMoZ 

try to 

1) add a calculated table with this:

Table2 = 
ADDCOLUMNS(
    CALENDAR(DATE(2023,1,1), DATE(2023,1,15)),
    "DD/MM",
    FORMAT([date], "DD/MM")
)

(No need to relate two tables)

2) plot a matrix visual with a measure like this:

Measure = 
VAR _date = MAX(table2[date])
RETURN
SUMX(
    FILTER(table1,table1[Date From]<=_date&&table1[Date To]>=_date),
    table1[Value]
)

i tried and it worked like this: