need help with dax - sum in date range

[parry2k]

Hi there,

I have two tables, one called location and other table is called transactions which has transaction count, trnasaction is linked to Location table on location key.

Location table has two measures to calculate date range, Start Date and End Date (these are measures get calculated based on some business logic)

Now I want to calculate sum of transaction from transaction table where transaction date is between Loation start and end date (measures) and here is what I tried.

MTD New = CALCULATE(SUM('Transaction'[Transaction Count]), DATESBETWEEN('Transaction'[Tran Date], [m MTD Location Start Date Current], [m MTD Location End Date Current]))

Issues:

1. I added a table, added location key and MTD New Measure, I get correct values for MTD New but total at the bottom is totally different.

2. If I put MTD New as card, i don't get correct sum. 

Not sure what I'm doing wrong here, any help will be appreciated.

PS - Location date range start and end date is a measure not a column and I'm thinking it has something to do with my calculation.

Thanks,

P

[mattbrice]

Try:

measure =
SUMX ( VALUES ( Location[Location key] ), [MTD New] )