Sum Amounts Between 2 Dates (diff example)

Hello,

Not sure if there is a simpler solution to this one but got help and here is a solution with measures and running totals.

There is a date table, which is linked to volumes as described above:

- active DateTable.Date to Volumes.WeekTo

- inactive DateTable.Date to Volumes.WeekFrom

Measure 1:

Vol In = CALCULATE(SUM(Volumes[Volume]), USERELATIONSHIP(Volumes[Week From], DateTable[Date]),FILTER(ALL(DateTable[Date]), DateTable[Date]<=MAX(DateTable[Date])))

This returns the sum of all Per Day volumes for the week and since it is <=, numbers show up in against their corresponding dates.

Measure 2:

Vol Out = CALCULATE(SUM(Volumes[Volume]), FILTER(ALL(DateTable[Date]), DateTable[Date]<MAX(DateTable[Date])))

This returns the same as above but since it is <, numbers appear in the next period.

Measure 3 (the one needed) i.e. average volume per day for each day:

Volume Calc = DIVIDE([Vol In]-[Vol Out],7,0)

What happens is that Volume In adds up volumes for Week1+Week2+Week3 etc

Volume Out does the same but with 1 week delay, so Vol In - Vol Out actually gets the Volume for the most recent week.

Hope this makes sense. Took me some time to understand.

Cheers