Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
9 years ago
Solved

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...
Need Help
  • bdymit's avatar
    bdymit
    9 years ago
    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:

SivaMani's avatar
SivaMani
Icon for Resident Rockstar rankResident Rockstar
9 years ago

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.