Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Add table column that sum between dates using DATESBETWEEN - only returns value for current row

I've seen quite a lot of suggestions using the below approach on the internet, also on Microsoft's site - however, when I'm attempting to insert a custom column in a table to sum values across a period of time, it's only returning the value of the current row (can be seen in image further down). Here's the snippet used:

value sum = CALCULATE (
    SUM ('Table'[Value]); 
    DATESBETWEEN ('Table'[Date]; DATE (2019;1;1); DATE (2019;1;30))
    )

I have a table called "Table" (surprise) and inserted the above snippet. The result is as below: 

 

 

I've seen multiple sites stating taht this should SUM the values inbetween the dates, but for whatever reason I can't make it work? I've checked that there are no relations between the table in question and any others.

Am I missing something or 

Need Help
  • v-eachen-msft's avatar
    v-eachen-msft
    6 years ago

    Hi Anonymous ,

     

    You dax is correct but it should be a measure. If you want use calculated column to show the sum, you can refer to the following dax:

    Column =
CALCULATE (
    SUM ( 'Table'[Value] ),
    DATESBETWEEN ( 'Table'[Date], DATE ( 2019, 01, 01 ), DATE ( 2019, 01, 31 ) ),
    ALL ( 'Table' )
)

    or

     

    Column 2 =
SUMX (
    FILTER (
        'Table',
        'Table'[Date] >= DATE ( 2019, 01, 01 )
            && 'Table'[Date] <= DATE ( 2019, 01, 31 )
    ),
    'Table'[Value]
)

    Here is the result.

    The difference between measure and calculated column can be found in this link:

    https://community.powerbi.com/t5/Desktop/column-vs-measure/td-p/13201

     

1 Reply

  • v-eachen-msft's avatar
    v-eachen-msft
    Community Support
    6 years ago

    Hi Anonymous ,

     

    You dax is correct but it should be a measure. If you want use calculated column to show the sum, you can refer to the following dax:

    Column =
CALCULATE (
    SUM ( 'Table'[Value] ),
    DATESBETWEEN ( 'Table'[Date], DATE ( 2019, 01, 01 ), DATE ( 2019, 01, 31 ) ),
    ALL ( 'Table' )
)

    or

     

    Column 2 =
SUMX (
    FILTER (
        'Table',
        'Table'[Date] >= DATE ( 2019, 01, 01 )
            && 'Table'[Date] <= DATE ( 2019, 01, 31 )
    ),
    'Table'[Value]
)

    Here is the result.

    The difference between measure and calculated column can be found in this link:

    https://community.powerbi.com/t5/Desktop/column-vs-measure/td-p/13201