Forum Discussion

slauer12's avatar
slauer12
Frequent Visitor
6 years ago
Solved

Calculate sum between dates

I am trying to calculate the rate column in Table 2 but as a measure instead of a predefined value. The rate column is a sum of the percent column in Table 1 between the Project Start Date (Table 2) and the Start Date (Table 1). Where I am getting stumped is that the Start Date in Table 1 varies and needs to be determined by a slicer which would then make the rate column vary as well. The rate column shown in the picture is calculated from the Project Start Date up to 10/1/2020 (ie Project A would be .5+1.2+3.5+.7+1.5+1.4 = 10.4)

 

 

 

Below is what I currently have but its not quite right as it totals all the way through the list instead of stopping at a certain date. 

 

Test = 
IF(
  MAX('Table2'[Project Start])>=TODAY(),
    CALCULATE(SUM('Table1'[Percent]),FILTER(ALL('Table1'),'Table1'[Start Date] >=TODAY()-30)), 
    CALCULATE(SUM('Table1'[Percent]),FILTER(ALL('Table1'),'Table1'[Start Date] >=MAX('Table2'[Project Start])-30)))

 

 
Any thoughts on how to stop the total at a date selected by a slicer? Thanks.  
  • I ended up solving the issue myself using the following measure. The hard coded -90 is dependant on the time period that you have between your dates and if you want to include the beginning date number as well. 

     

    Escalated Cost = CALCULATE(([Escalation]*SUM(Sheet2[Cost]))+SUM(Sheet2[Cost]),DATESBETWEEN(Sheet1[Start Date],MAX(Sheet2[Project Start])-90,MAX(Sheet1[Start Date])))

     

5 Replies