Sum Amounts Between 2 Dates (diff example)

[ChavdarG]

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

[AlB]

@ChavdarG 

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.

[AlB]

@ChavdarG 

If you don't want the slicer, just select the month to August 2020 in a visual filter for the table.

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.

[AlB]

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.

[ChavdarG]

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/24

[ChavdarG]

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.

[ChavdarG · Accepted solution]

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