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
Hi ChavdarG
If you share the data above in text-tabular format, so that it can be copied, I will give you a possible solution
|
|
Please accept the solution when done and consider giving a thumbs up if posts are helpful. Contact me privately for support with any larger-scale BI needs, tutoring, etc. |
Hello AlB,
Thank you for the swift response.
The thing is I do not have to firter / select anything. I need to have the table show me for each day from 08/01/2020 to 08/31/2021 what is the average sales per day. My second step is to add customers and products but for now this is not important.
I am putting here an extract from the data for 1 product only for several customers for part of the period.
So the table I am looking for (w/o any selections) is simply the daily averages (I have a date table created):
08/01/2020 xxx
08/02/2020 yyy
08/03/2020 zzz
...
08/31/2021 aaa
Thank you
Customer Product Volume WeekFrom WeekTo
1 1 180 2020/07/26 2020/08/01
1 1 540 2020/07/26 2020/08/01
1 1 0 2020/07/26 2020/08/01
1 1 180 2020/07/26 2020/08/01
1 1 270 2020/08/02 2020/08/08
1 1 360 2020/08/02 2020/08/08
1 1 360 2020/08/09 2020/08/15
1 1 270 2020/08/09 2020/08/15
1 1 180 2020/08/09 2020/08/15
1 1 180 2020/08/16 2020/08/22
1 1 180 2020/08/16 2020/08/22
1 1 0 2020/08/16 2020/08/22
1 1 180 2020/08/23 2020/08/29
1 1 270 2020/08/23 2020/08/29
1 1 270 2020/08/23 2020/08/29
1 1 270 2020/08/30 2020/09/05
1 1 0 2020/08/30 2020/09/05
1 1 360 2020/08/30 2020/09/05
1 1 270 2020/08/30 2020/09/05
1 1 270 2020/08/30 2020/09/05
1 1 0 2020/09/06 2020/09/12
1 1 360 2020/09/06 2020/09/12
1 1 180 2020/09/13 2020/09/19
1 1 180 2020/09/13 2020/09/19
1 1 270 2020/09/13 2020/09/19
1 1 360 2020/09/20 2020/09/26
1 1 180 2020/09/20 2020/09/26
1 1 360 2020/09/20 2020/09/26
1 1 360 2020/09/27 2020/10/03
1 1 180 2020/09/27 2020/10/03
1 1 180 2020/09/27 2020/10/03
1 1 180 2020/10/04 2020/10/10
1 1 360 2020/10/04 2020/10/10
1 1 180 2020/10/11 2020/10/17
1 1 270 2020/10/11 2020/10/17
1 1 360 2020/10/11 2020/10/17
1 1 360 2020/10/18 2020/10/24
1 1 180 2020/10/18 2020/10/24
1 1 0 2020/10/18 2020/10/24
1 1 180 2020/10/18 2020/10/24
1 1 180 2020/10/25 2020/10/31
1 1 360 2020/10/25 2020/10/31
1 1 360 2020/10/25 2020/10/31
1 1 270 2020/11/01 2020/11/07
1 1 450 2020/11/01 2020/11/07
1 1 360 2020/11/01 2020/11/07
1 1 270 2020/11/08 2020/11/14
1 1 180 2020/11/08 2020/11/14
1 1 180 2020/11/08 2020/11/14
1 1 180 2020/11/15 2020/11/21
1 1 0 2020/11/15 2020/11/21
1 1 360 2020/11/15 2020/11/21
1 1 540 2020/11/15 2020/11/21
1 1 270 2020/11/22 2020/11/28
1 1 180 2020/11/22 2020/11/28
1 1 180 2020/11/29 2020/12/05
1 1 270 2020/11/29 2020/12/05
1 1 360 2020/12/06 2020/12/12
1 1 270 2020/12/06 2020/12/12
1 1 270 2020/12/06 2020/12/12
1 1 270 2020/12/13 2020/12/19
1 1 270 2020/12/20 2020/12/26
1 1 270 2020/12/20 2020/12/26
1 1 270 2020/12/20 2020/12/26
1 1 0 2020/12/20 2020/12/26
2 1 0 2020/08/02 2020/08/08
2 1 18 2020/08/02 2020/08/08
2 1 9 2020/08/09 2020/08/15
2 1 0 2020/08/09 2020/08/15
2 1 9 2020/08/09 2020/08/15
2 1 18 2020/08/30 2020/09/05
2 1 18 2020/09/06 2020/09/12
2 1 36 2020/09/13 2020/09/19
2 1 9 2020/09/13 2020/09/19
2 1 27 2020/09/20 2020/09/26
2 1 18 2020/09/27 2020/10/03
2 1 0 2020/10/04 2020/10/10
2 1 9 2020/10/04 2020/10/10
2 1 9 2020/10/11 2020/10/17
2 1 9 2020/10/11 2020/10/17
2 1 9 2020/10/25 2020/10/31
2 1 0 2020/11/01 2020/11/07
2 1 9 2020/11/01 2020/11/07
2 1 0 2020/11/08 2020/11/14
2 1 9 2020/11/15 2020/11/21
2 1 9 2020/11/15 2020/11/21
2 1 9 2020/11/22 2020/11/28
2 1 0 2020/11/29 2020/12/05
2 1 9 2020/11/29 2020/12/05
2 1 9 2020/11/29 2020/12/05
2 1 9 2020/12/06 2020/12/12
2 1 9 2020/12/06 2020/12/12
2 1 9 2020/12/13 2020/12/19
2 1 9 2020/12/13 2020/12/19
2 1 9 2020/12/13 2020/12/19
2 1 9 2020/12/13 2020/12/19
2 1 0 2020/12/20 2020/12/26
3 1 9 2020/09/13 2020/09/19
3 1 9 2020/09/13 2020/09/19
3 1 9 2020/09/20 2020/09/26
4 1 0 2020/07/26 2020/08/01
4 1 45 2020/08/02 2020/08/08
4 1 0 2020/08/09 2020/08/15
4 1 45 2020/08/09 2020/08/15
4 1 0 2020/08/09 2020/08/15
4 1 45 2020/08/16 2020/08/22
4 1 0 2020/08/16 2020/08/22
4 1 45 2020/08/23 2020/08/29
4 1 45 2020/08/30 2020/09/05
4 1 45 2020/08/30 2020/09/05
4 1 90 2020/09/06 2020/09/12
4 1 0 2020/09/13 2020/09/19
4 1 45 2020/09/20 2020/09/26
4 1 45 2020/09/20 2020/09/26
4 1 45 2020/09/27 2020/10/03
4 1 90 2020/10/04 2020/10/10
4 1 45 2020/10/04 2020/10/10
4 1 45 2020/10/11 2020/10/17
4 1 90 2020/10/18 2020/10/24
4 1 45 2020/10/18 2020/10/24
4 1 0 2020/10/25 2020/10/31
4 1 90 2020/10/25 2020/10/31
4 1 135 2020/10/25 2020/10/31
4 1 0 2020/11/01 2020/11/07
4 1 45 2020/11/01 2020/11/07
4 1 45 2020/11/08 2020/11/14
4 1 45 2020/11/08 2020/11/14
4 1 45 2020/11/15 2020/11/21
4 1 0 2020/11/15 2020/11/21
4 1 45 2020/11/22 2020/11/28
4 1 0 2020/11/22 2020/11/28
4 1 90 2020/11/29 2020/12/05
4 1 0 2020/11/29 2020/12/05
4 1 90 2020/12/06 2020/12/12
4 1 0 2020/12/06 2020/12/12
4 1 45 2020/12/06 2020/12/12
4 1 90 2020/12/13 2020/12/19
4 1 0 2020/12/13 2020/12/19
4 1 45 2020/12/13 2020/12/19
4 1 0 2020/12/20 2020/12/26
4 1 0 2020/12/20 2020/12/26
5 1 9 2020/07/26 2020/08/01
5 1 0 2020/08/16 2020/08/22
6 1 0 2020/07/26 2020/08/01
6 1 270 2020/07/26 2020/08/01
6 1 180 2020/07/26 2020/08/01
6 1 180 2020/08/02 2020/08/08
6 1 450 2020/08/02 2020/08/08
6 1 450 2020/08/02 2020/08/08
6 1 0 2020/08/09 2020/08/15
6 1 360 2020/08/09 2020/08/15
6 1 0 2020/08/16 2020/08/22
6 1 0 2020/08/23 2020/08/29
6 1 0 2020/08/23 2020/08/29
6 1 180 2020/08/23 2020/08/29
6 1 0 2020/08/30 2020/09/05
6 1 180 2020/08/30 2020/09/05
6 1 270 2020/08/30 2020/09/05
6 1 180 2020/08/30 2020/09/05
6 1 360 2020/09/06 2020/09/12
6 1 450 2020/09/06 2020/09/12
6 1 0 2020/09/13 2020/09/19
6 1 270 2020/09/20 2020/09/26
6 1 180 2020/09/20 2020/09/26
6 1 180 2020/09/27 2020/10/03
6 1 0 2020/09/27 2020/10/03
6 1 180 2020/09/27 2020/10/03
6 1 0 2020/10/04 2020/10/10
6 1 360 2020/10/04 2020/10/10
6 1 180 2020/10/04 2020/10/10
6 1 180 2020/10/11 2020/10/17
6 1 180 2020/10/11 2020/10/17
6 1 180 2020/10/11 2020/10/17
6 1 0 2020/10/18 2020/10/24
6 1 180 2020/10/18 2020/10/24
6 1 180 2020/10/18 2020/10/24
6 1 0 2020/10/25 2020/10/31
6 1 180 2020/10/25 2020/10/31
6 1 180 2020/10/25 2020/10/31
6 1 0 2020/10/25 2020/10/31
6 1 0 2020/11/01 2020/11/07
6 1 0 2020/11/01 2020/11/07
6 1 180 2020/11/08 2020/11/14
6 1 0 2020/11/08 2020/11/14
6 1 180 2020/11/08 2020/11/14
6 1 180 2020/11/08 2020/11/14
6 1 180 2020/11/15 2020/11/21
6 1 180 2020/11/15 2020/11/21
6 1 180 2020/11/15 2020/11/21
6 1 180 2020/11/22 2020/11/28
6 1 0 2020/11/22 2020/11/28
6 1 0 2020/11/29 2020/12/05
6 1 0 2020/11/29 2020/12/05
6 1 180 2020/11/29 2020/12/05
6 1 180 2020/12/06 2020/12/12
6 1 0 2020/12/06 2020/12/12
6 1 180 2020/12/06 2020/12/12
6 1 180 2020/12/13 2020/12/19
6 1 0 2020/12/13 2020/12/19
6 1 360 2020/12/13 2020/12/19
6 1 0 2020/12/13 2020/12/19
6 1 0 2020/12/20 2020/12/26
6 1 270 2020/12/20 2020/12/26
7 1 90 2020/07/26 2020/08/01
7 1 0 2020/07/26 2020/08/01
7 1 90 2020/08/02 2020/08/08
7 1 0 2020/08/02 2020/08/08
7 1 90 2020/08/02 2020/08/08
7 1 0 2020/08/09 2020/08/15
7 1 90 2020/08/09 2020/08/15
7 1 0 2020/08/16 2020/08/22
7 1 90 2020/08/16 2020/08/22
7 1 90 2020/08/16 2020/08/22
7 1 90 2020/08/23 2020/08/29
7 1 0 2020/08/30 2020/09/05
7 1 90 2020/08/30 2020/09/05
7 1 90 2020/08/30 2020/09/05
7 1 0 2020/09/06 2020/09/12
7 1 90 2020/09/13 2020/09/19
7 1 90 2020/09/13 2020/09/19
7 1 90 2020/09/20 2020/09/26
7 1 90 2020/09/20 2020/09/26
7 1 90 2020/09/20 2020/09/26
7 1 90 2020/09/27 2020/10/03
7 1 90 2020/09/27 2020/10/03
7 1 0 2020/10/04 2020/10/10
7 1 90 2020/10/04 2020/10/10
7 1 90 2020/10/04 2020/10/10
7 1 0 2020/10/11 2020/10/17
7 1 90 2020/10/11 2020/10/17
7 1 90 2020/10/18 2020/10/24
7 1 0 2020/10/18 2020/10/24
7 1 90 2020/10/18 2020/10/24
7 1 0 2020/10/18 2020/10/24
7 1 0 2020/10/18 2020/10/24
7 1 90 2020/10/25 2020/10/31
7 1 0 2020/10/25 2020/10/31
7 1 0 2020/10/25 2020/10/31
7 1 90 2020/11/01 2020/11/07
7 1 0 2020/11/01 2020/11/07
7 1 90 2020/11/01 2020/11/07
7 1 90 2020/11/08 2020/11/14
7 1 0 2020/11/08 2020/11/14
7 1 0 2020/11/08 2020/11/14
7 1 90 2020/11/08 2020/11/14
7 1 90 2020/11/15 2020/11/21
7 1 90 2020/11/15 2020/11/21
7 1 90 2020/11/22 2020/11/28
7 1 0 2020/11/22 2020/11/28
7 1 90 2020/11/22 2020/11/28
7 1 0 2020/11/29 2020/12/05
7 1 90 2020/11/29 2020/12/05
7 1 90 2020/12/06 2020/12/12
7 1 0 2020/12/06 2020/12/12
7 1 0 2020/12/13 2020/12/19
7 1 90 2020/12/13 2020/12/19
7 1 0 2020/12/13 2020/12/19
7 1 90 2020/12/20 2020/12/26
7 1 0 2020/12/20 2020/12/26
7 1 270 2020/12/20 2020/12/26
7 1 135 2020/07/26 2020/08/01
7 1 135 2020/07/26 2020/08/01
7 1 90 2020/07/26 2020/08/01
7 1 315 2020/08/02 2020/08/08
7 1 135 2020/08/02 2020/08/08
7 1 135 2020/08/02 2020/08/08
7 1 135 2020/08/09 2020/08/15
7 1 135 2020/08/09 2020/08/15
7 1 135 2020/08/09 2020/08/15
7 1 135 2020/08/16 2020/08/22
7 1 135 2020/08/16 2020/08/22
7 1 135 2020/08/23 2020/08/29
7 1 315 2020/08/23 2020/08/29
7 1 135 2020/08/30 2020/09/05
7 1 180 2020/08/30 2020/09/05
7 1 135 2020/09/06 2020/09/12
7 1 180 2020/09/06 2020/09/12
7 1 90 2020/09/06 2020/09/12
7 1 90 2020/09/13 2020/09/19
7 1 315 2020/09/13 2020/09/19
7 1 135 2020/09/20 2020/09/26
7 1 180 2020/09/20 2020/09/26
7 1 90 2020/09/20 2020/09/26
7 1 135 2020/09/27 2020/10/03
7 1 180 2020/09/27 2020/10/03
7 1 90 2020/09/27 2020/10/03
7 1 135 2020/10/04 2020/10/10
7 1 135 2020/10/04 2020/10/10
7 1 90 2020/10/04 2020/10/10
7 1 90 2020/10/11 2020/10/17
7 1 135 2020/10/11 2020/10/17
7 1 180 2020/10/11 2020/10/17
7 1 135 2020/10/18 2020/10/24
7 1 225 2020/10/18 2020/10/24
7 1 180 2020/10/18 2020/10/24
7 1 135 2020/10/25 2020/10/31
7 1 225 2020/10/25 2020/10/31
7 1 180 2020/10/25 2020/10/31
7 1 180 2020/11/01 2020/11/07
7 1 180 2020/11/01 2020/11/07
7 1 180 2020/11/01 2020/11/07
7 1 180 2020/11/08 2020/11/14
7 1 180 2020/11/08 2020/11/14
7 1 180 2020/11/08 2020/11/14
7 1 180 2020/11/15 2020/11/21
7 1 180 2020/11/15 2020/11/21
7 1 90 2020/11/22 2020/11/28
7 1 90 2020/11/29 2020/12/05
7 1 90 2020/11/29 2020/12/05
7 1 135 2020/12/06 2020/12/12
7 1 135 2020/12/06 2020/12/12
7 1 90 2020/12/13 2020/12/19
7 1 135 2020/12/13 2020/12/19
7 1 135 2020/12/13 2020/12/19
7 1 135 2020/12/20 2020/12/26
7 1 135 2020/12/20 2020/12/26
7 1 225 2020/12/20 2020/12/26
7 1 90 2020/07/26 2020/08/01
7 1 0 2020/07/26 2020/08/01
7 1 90 2020/07/26 2020/08/01
7 1 90 2020/07/26 2020/08/01
7 1 90 2020/08/02 2020/08/08
7 1 90 2020/08/09 2020/08/15
7 1 0 2020/08/09 2020/08/15
7 1 90 2020/08/09 2020/08/15
7 1 90 2020/08/16 2020/08/22
7 1 90 2020/08/16 2020/08/22
7 1 90 2020/08/16 2020/08/22
7 1 0 2020/08/23 2020/08/29
7 1 90 2020/08/23 2020/08/29
7 1 0 2020/08/23 2020/08/29
7 1 90 2020/08/30 2020/09/05
7 1 90 2020/08/30 2020/09/05
7 1 90 2020/09/06 2020/09/12
7 1 90 2020/09/06 2020/09/12
7 1 0 2020/09/13 2020/09/19
7 1 0 2020/09/13 2020/09/19
7 1 90 2020/09/13 2020/09/19
7 1 90 2020/09/13 2020/09/19
7 1 90 2020/09/20 2020/09/26
7 1 90 2020/09/20 2020/09/26
7 1 0 2020/09/27 2020/10/03
7 1 90 2020/09/27 2020/10/03
7 1 90 2020/10/04 2020/10/10
7 1 90 2020/10/04 2020/10/10
7 1 90 2020/10/04 2020/10/10
7 1 90 2020/10/11 2020/10/17
7 1 90 2020/10/11 2020/10/17
7 1 0 2020/10/18 2020/10/24
7 1 90 2020/10/18 2020/10/24
7 1 0 2020/10/18 2020/10/24
7 1 90 2020/10/18 2020/10/24
7 1 90 2020/10/25 2020/10/31
7 1 90 2020/10/25 2020/10/31
7 1 90 2020/10/25 2020/10/31
7 1 90 2020/11/01 2020/11/07
7 1 90 2020/11/08 2020/11/14
7 1 90 2020/11/08 2020/11/14
7 1 90 2020/11/15 2020/11/21
7 1 90 2020/11/15 2020/11/21
7 1 90 2020/11/22 2020/11/28
7 1 90 2020/11/22 2020/11/28
7 1 90 2020/11/29 2020/12/05
7 1 90 2020/11/29 2020/12/05
7 1 0 2020/12/06 2020/12/12
7 1 180 2020/12/06 2020/12/12
7 1 90 2020/12/06 2020/12/12
7 1 0 2020/12/13 2020/12/19
7 1 90 2020/12/13 2020/12/19
7 1 90 2020/12/13 2020/12/19
7 1 90 2020/12/20 2020/12/26
8 1 45 2020/07/26 2020/08/01
8 1 0 2020/07/26 2020/08/01
8 1 90 2020/07/26 2020/08/01
8 1 45 2020/07/26 2020/08/01
8 1 45 2020/08/02 2020/08/08
8 1 90 2020/08/02 2020/08/08
8 1 0 2020/08/02 2020/08/08
8 1 45 2020/08/02 2020/08/08
8 1 90 2020/08/02 2020/08/08
8 1 45 2020/08/09 2020/08/15
8 1 0 2020/08/09 2020/08/15
8 1 90 2020/08/09 2020/08/15
8 1 18 2020/08/16 2020/08/22
8 1 45 2020/08/16 2020/08/22
8 1 0 2020/08/16 2020/08/22
8 1 180 2020/08/23 2020/08/29
8 1 0 2020/08/23 2020/08/29
8 1 90 2020/08/23 2020/08/29
8 1 45 2020/08/23 2020/08/29
8 1 135 2020/08/30 2020/09/05
8 1 45 2020/08/30 2020/09/05
8 1 45 2020/08/30 2020/09/05
8 1 45 2020/08/30 2020/09/05
8 1 45 2020/09/06 2020/09/12
8 1 117 2020/09/06 2020/09/12
8 1 36 2020/09/13 2020/09/19
8 1 72 2020/09/13 2020/09/19
8 1 90 2020/09/13 2020/09/19
8 1 135 2020/09/20 2020/09/26
8 1 90 2020/09/20 2020/09/26
8 1 45 2020/09/20 2020/09/26
8 1 90 2020/09/27 2020/10/03
8 1 0 2020/09/27 2020/10/03
8 1 45 2020/09/27 2020/10/03
8 1 90 2020/09/27 2020/10/03
8 1 45 2020/10/04 2020/10/10
8 1 90 2020/10/04 2020/10/10
8 1 45 2020/10/04 2020/10/10
8 1 45 2020/10/11 2020/10/17
8 1 45 2020/10/11 2020/10/17
8 1 0 2020/10/11 2020/10/17
8 1 135 2020/10/18 2020/10/24
8 1 135 2020/10/18 2020/10/24
8 1 0 2020/10/18 2020/10/24
8 1 72 2020/10/18 2020/10/24
8 1 45 2020/10/25 2020/10/31
8 1 90 2020/10/25 2020/10/31
8 1 45 2020/10/25 2020/10/31
8 1 45 2020/10/25 2020/10/31
8 1 90 2020/11/01 2020/11/07
8 1 90 2020/11/01 2020/11/07
8 1 90 2020/11/08 2020/11/14
8 1 0 2020/11/08 2020/11/14
8 1 0 2020/11/08 2020/11/14
8 1 90 2020/11/08 2020/11/14
8 1 180 2020/11/08 2020/11/14
8 1 90 2020/11/15 2020/11/21
8 1 135 2020/11/22 2020/11/28
8 1 45 2020/11/22 2020/11/28
8 1 0 2020/11/29 2020/12/05
8 1 90 2020/11/29 2020/12/05
8 1 45 2020/12/06 2020/12/12
8 1 90 2020/12/06 2020/12/12
8 1 45 2020/12/06 2020/12/12
8 1 0 2020/12/06 2020/12/12
8 1 135 2020/12/13 2020/12/19
8 1 90 2020/12/13 2020/12/19
8 1 45 2020/12/20 2020/12/26
8 1 90 2020/12/20 2020/12/26
7 1 360 2020/07/26 2020/08/01
7 1 1080 2020/07/26 2020/08/01
7 1 360 2020/07/26 2020/08/01
7 1 900 2020/07/26 2020/08/01
7 1 900 2020/08/02 2020/08/08
7 1 540 2020/08/02 2020/08/08
7 1 180 2020/08/02 2020/08/08
7 1 270 2020/08/09 2020/08/15
7 1 180 2020/08/09 2020/08/15
7 1 540 2020/08/09 2020/08/15
7 1 2700 2020/08/16 2020/08/22
7 1 2700 2020/08/16 2020/08/22
7 1 1800 2020/08/16 2020/08/22
7 1 1350 2020/08/23 2020/08/29
7 1 720 2020/08/23 2020/08/29
7 1 1800 2020/08/23 2020/08/29
7 1 450 2020/08/30 2020/09/05
7 1 630 2020/08/30 2020/09/05
7 1 1800 2020/08/30 2020/09/05
7 1 900 2020/09/06 2020/09/12
7 1 450 2020/09/06 2020/09/12
7 1 900 2020/09/06 2020/09/12
7 1 900 2020/09/13 2020/09/19
7 1 450 2020/09/13 2020/09/19
7 1 270 2020/09/20 2020/09/26
7 1 180 2020/09/20 2020/09/26
7 1 360 2020/09/20 2020/09/26
7 1 360 2020/09/20 2020/09/26
7 1 360 2020/09/27 2020/10/03
7 1 180 2020/09/27 2020/10/03
7 1 180 2020/09/27 2020/10/03
7 1 540 2020/09/27 2020/10/03
7 1 3600 2020/10/04 2020/10/10
7 1 450 2020/10/04 2020/10/10
7 1 450 2020/10/04 2020/10/10
7 1 900 2020/10/04 2020/10/10
7 1 450 2020/10/11 2020/10/17
7 1 360 2020/10/11 2020/10/17
7 1 360 2020/10/11 2020/10/17
7 1 900 2020/10/11 2020/10/17
7 1 270 2020/10/18 2020/10/24
7 1 180 2020/10/18 2020/10/24
7 1 360 2020/10/18 2020/10/24
7 1 180 2020/10/25 2020/10/31
7 1 360 2020/10/25 2020/10/31
7 1 450 2020/10/25 2020/10/31
7 1 270 2020/11/01 2020/11/07
7 1 270 2020/11/01 2020/11/07
7 1 990 2020/11/01 2020/11/07
7 1 360 2020/11/08 2020/11/14
7 1 180 2020/11/08 2020/11/14
7 1 270 2020/11/08 2020/11/14
7 1 720 2020/11/08 2020/11/14
7 1 360 2020/11/15 2020/11/21
7 1 180 2020/11/15 2020/11/21
7 1 360 2020/11/15 2020/11/21
7 1 720 2020/11/15 2020/11/21
7 1 450 2020/11/22 2020/11/28
7 1 900 2020/11/22 2020/11/28
7 1 450 2020/11/29 2020/12/05
7 1 450 2020/11/29 2020/12/05
7 1 450 2020/11/29 2020/12/05
7 1 450 2020/11/29 2020/12/05
7 1 180 2020/12/06 2020/12/12
7 1 180 2020/12/06 2020/12/12
7 1 720 2020/12/06 2020/12/12
7 1 180 2020/12/13 2020/12/19
7 1 180 2020/12/13 2020/12/19
7 1 360 2020/12/13 2020/12/19
7 1 450 2020/12/20 2020/12/26
7 1 450 2020/12/20 2020/12/26
7 1 900 2020/12/20 2020/12/26
9 1 9 2020/10/04 2020/10/10
9 1 0 2020/10/18 2020/10/24Just 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