Forum Discussion
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
Just wanted to make a clarification: I think I need a calculated column in the DateTable.
Get the Vol per Day for each day.
The DateTable is linked to the Volumes table with 2 connections:
- active DateTable.Date to Volumes.WeekTo
- inactive DateTable.Date to Volumes.WeekFrom
If I use this DAX:
Column = CALCULATE(SUM(Volumes[Vol per Day]), FILTER(Volumes, LOOKUPVALUE(DateTable_FROM[Date], DateTable_FROM[DateInt], DateTable[DateInt])>=EARLIER(DateTable[Date]) && Volumes[Week To]<=EARLIER(DateTable[Date])))
Basically this returns a sum of all volumes per day based on the WeekTo date because the other relation is inactive.
I need to somehow meet both conditions and then there will be only one Vol per Day on each row.
Hope it's clear.
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