Measure that sums values between two different date fields - pbix example attached

[Anonymous]

I have a table with three fields: start date, end date, and a value. I want a measure that will sum the value if the date is between the start and end date of the row.

I can create the visual I want in excel - see picture below. But I'm stuck on how to create a measure in PowerBI. I do have a date table but I'm unclear if I should link up the date table to the start and end date?

Here is an example .pbix file: https://1drv.ms/u/s!AvCirXUmRp-JhcBx08tLAQrig-XM-A?e=XxukAt

 

[mahoneypat · Accepted solution]

Here is one way to do it, with no relationship between the tables.

NewMeasure =
VAR thisdate =
    MIN ( 'Date Table'[Date] )
RETURN
    CALCULATE (
        SUM ( 'Data Table'[Value] ),
        'Data Table'[Start Date] <= thisdate,
        'Data Table'[End Date] >= thisdate
    )

Regards,

Pat