Forum Discussion
Calculate Totals for Dynamic Date Range
Hi,
I have a table as imported from an excel file with two columns, Date and Amount. I have created a calculated column for a date 3 days in the past using the DAX formula StartDate = Sheet1[Date] = 3
I am trying to show an additional measure L3D Amount which would be a sum of the amount for the last 3 days, or however many days are present. Please see what I am trying to accomplish below. How would I be able to do this? Thanks
| Date | Amount | L3D Amount | |
| 31/01/1999 | 10,000.00 | 10,000.00 | Sum of amount for 31/01/2017 |
| 01/07/2017 | 4,974.00 | 4,974.00 | Sum of amount for 01/07/2017 |
| 02/07/2017 | 5,475.00 | 10,449.00 | Sum of amount for 01/07/2017, 02/07/2017 |
| 03/07/2017 | 9,305.00 | 19,754.00 | Sum of amount for 01/07/2017, 02/01/2017, 03/07/2017 |
| 04/07/2017 | 3,169.00 | 17,949.00 | Sum of amount for 02/07/2017, 03/01/2017, 04/07/2017 |
| 05/07/2017 | 2,649.00 | 15,123.00 | Sum of amount for 03/07/2017, 04/01/2017, 05/07/2017 |
| 06/07/2017 | 2,585.00 | 8,403.00 | etc. |
| 07/07/2017 | 7,572.00 | 12,806.00 | |
| 08/07/2017 | 9,255.00 | 19,412.00 | |
| 09/07/2017 | 3,433.00 | 20,260.00 | |
| 10/07/2017 | 3,349.00 | 16,037.00 |
L3D Amount :=
VAR enddate =
MAX ( Table1[Date] )
VAR startdate = enddate - 2
RETURN
CALCULATE (
[Sum of Amount],
Table1[Date] >= startdate
&& Table1[Date] <= enddate
)Otherwise, if your version of Excel/ DAX does not support variables
L3D Amount No VAR := CALCULATE ( [Sum of Amount], FILTER ( ALL ( Table1[Date] ), Table1[Date] <= MAX ( Table1[Date] ) && Table1[Date] >= MAX ( Table1[Date] ) - 2 ) )Both of the above are measures, you should not need your calculated column where you found the day that was 3 days previous.
In general, for values you want displayed within a visualization/Pivot Table/ etc., measures are more efficient than calculated columns because:
1. They do not take up physical space in your Data Model, which increases file size and memory used.
2. Calculated columns are calculated at refresh, taking up more time at refresh. Measures are only calculated when you place them in a visual/Pivot Table.
3. Measures are flexible, and you can change context with which you are seeing them very easily.
Let me know if this helps!
Result:
5 Replies
- bdymit
Resolver II
L3D Amount :=
VAR enddate =
MAX ( Table1[Date] )
VAR startdate = enddate - 2
RETURN
CALCULATE (
[Sum of Amount],
Table1[Date] >= startdate
&& Table1[Date] <= enddate
)Otherwise, if your version of Excel/ DAX does not support variables
L3D Amount No VAR := CALCULATE ( [Sum of Amount], FILTER ( ALL ( Table1[Date] ), Table1[Date] <= MAX ( Table1[Date] ) && Table1[Date] >= MAX ( Table1[Date] ) - 2 ) )Both of the above are measures, you should not need your calculated column where you found the day that was 3 days previous.
In general, for values you want displayed within a visualization/Pivot Table/ etc., measures are more efficient than calculated columns because:
1. They do not take up physical space in your Data Model, which increases file size and memory used.
2. Calculated columns are calculated at refresh, taking up more time at refresh. Measures are only calculated when you place them in a visual/Pivot Table.
3. Measures are flexible, and you can change context with which you are seeing them very easily.
Let me know if this helps!
Result:
SivaManiResident Rockstar
Anonymous Did you create a calculated column or calculated Measure?
Just create a calculated column with this DAX,
L3D= CALCULATE(SUM(Table2[Amount]), DATESBETWEEN(Table2[Date],DATEADD(Table2[Date],-3,DAY),Table2[Date]))
Let me know if the issue still exists.