Forum Discussion
Calculate Totals for Dynamic Date Range
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:
Doesn't work, tried it already. It says a single value can't be determined and I need to use min, max, etc. to get a single value. Is it possible to do what I'm trying to do?
Resolver IIL3D 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: