Forum Discussion
Sum Amounts Between 2 Dates (diff example)
Dear all,
I haven't been able to figure this one out, so keeping fingers crossed someone here can.
I have to do an analysis around time-series and I am stuck at step one.
Hopefully you can help me move on and if I get stuck on the next steps will write again (it's not straightforward for me at all).
I have this simple initial data:
Volume per day is a measure calculated as Volume / ((WeekTo - WeekFrom)+1)
I currently need a table showing the average sales per day for each date between 08/01/2020 (that's Aug) to 08/31/2021.
In this example, 08/01/2020 will be 102.86 (25.71+25.71+51.43)
08/02/2020 will be 154.29 (77.14+38.57+38.57)
I have created a DateTable via a function and it contains all days in the period I need.
I will not bother you with all the ways I tried to link it to this one (including Referencing it twice as DateTable_FROM and DateTable_TO).
Starting clean, please help me out with a solution, so I can move on.
Thank you in advance,
Chavdar
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
6 Replies
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.
1. Place DateT[Date] in a table visual. Create a date table if you do not have one. No relationship to your fact table. Add a slicer to select August
2. Place this measure in the table visual:
Measure = VAR currentDate_ = SELECTEDVALUE ( DateT[Date] ) RETURN CALCULATE ( SUM ( Table1[Vol per Day] ), Table1[Week From] <= currentDate_, Table1[Week To] >= currentDate_ )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.
