distribute values in specific date range

Hmmm so this measure should in theory work

My Measure = CALCULATE (
    SUM ( 'NPS Sales and Target Capture'[Total Annual Target TEU] ),
    FILTER (
        ALL ( 'Date'[Date] ),
        'Date'[Date] <= MAX ( 'Table'[Date] )
    )
)

Try adding the DATE column from your DATE table to the axis rather than the date column from NPS Sales and Target Capture

Make two relationships.

The first will be between StartDate on the NPS table and Date on the Date table.  This relationship will be a solid line in the relationship diagram,

Then if you need a second relationship between the two tables, create it between the enddate on the NPS table and the Date table.  This will be a dotted line, or an inactive relationship.  This can be used in measures, but only specifically when mentioned.  You may not need this at all/

I'm heading out for the rest of the day but will try to have a look at this for you later tonight.

Hi Phil_Seamark

Yes already have. I connected [Date]'Date' to [NPS and Sales Target Capture]'Date'.

Hi Phil_Seamark

Sorry might have misunderstood it. 

Hmm.. How would I related my 2 fields in NPS (Start Date and End Date) to Date(date field) wherein Date table has only one field. Do you have any idea?

Thanks again!

Hi Phil_Seamark

Sorry might have misunderstood it. I don't think I have the relationship yet between NPS and Date table.

Hmm.. How would I related my 2 fields in NPS (Start Date and End Date) to Date(date field) wherein Date table has only one field. Do you have any idea?

Thanks again!

Hi Phil_Seamark

Awesome it worked! Thank you!

However, the only catch is, we can only set one relationship (active) for between two tables. For Start Date and End Date, the only active one is the 'Start Date' hence when I filter my date range, other filters in my report is not working properly. Do you have any thoughts on this?

Example:
Start Dates:
Jan. 31, 2017
Feb. 4, 2017

End Date:
June 13, 2017
May 24, 2017

When I select the filter to show March onwards(which is outside Start Date) records, the aggregation works properly but not the other filters. See screenshot.

Thanks Phil_Seamark! Appreciate it!

Hi junyetzotomayor,

Any chance you can send me a cut down version of your PBIX file?  Feel free to private message me the link. This will make it much easier for me to figure out.  :)

Cheers,

Phil