Forum Discussion
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)))
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
- Tara_Helper II
Hi slauer12 ,
Have you tried to filter table1 using ALLSELECTED instead of ALL?
This might help https://community.powerbi.com/t5/Desktop/What-s-the-difference-between-ALL-vs-ALLSELECTED-with-formula/td-p/185244
- Tara_Helper II
Can you share the pbix file (or the sample data you provided and the slicer) and are you using a Date table?