Forum Discussion

ChavdarG's avatar
ChavdarG
Frequent Visitor
4 years ago
Solved

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 m...
Need Help
  • ChavdarG's avatar
    ChavdarG
    4 years ago

    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

     

AlB's avatar
AlB
Community Champion
4 years ago

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.