Calculate Totals for Dynamic Date Range

[Anonymous]

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

[SivaMani]

@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.

[Anonymous]

You could try something like this to get the last 3 days summed:

=CALCULATE(SUM(Sheet1[Amount]),DATESBETWEEN(Sheet1[Date],DATEADD(Sheet1[Date],-2,DAY),Sheet1[Date]), all(Sheet1))

[Anonymous]

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?

[bdymit · Accepted solution]

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:

[Anonymous]

@bdymit

Works like a charm, thanks!