Create column of daily values summed across a month

[mbegg]

Hi, 

I have one column of values and one of dates (daily time series data).

I want to create a column with the monthly & annual sum of the values like the below.

What is the DAX code?

 

[GilbertQ · Accepted solution]

Hi @mbegg

You can use the following DAX below:

Current Month = CALCULATE(SUM('TableName'[Value]),PARALLELPERIOD('Date'[Calendar Date],0,MONTH))

Current Year = CALCULATE(SUM('TableName'[Value]),PARALLELPERIOD('Date'[Calendar Date],0,YEAR)) 

NOTE: You will have to have a Date table mapped to your data.

You can find more details around a Date table here: http://radacad.com/do-you-need-a-date-dimension

[mbegg]

Hi @GilbertQ, thank you, that works

[v-chuncz-msft]

@mbegg,

You could also add calculated columns as shown below.

Value Mth = 
CALCULATE (
    SUM ( Table1[Value] ),
    ALLEXCEPT ( Table1, Table1[Year], Table1[MonthNo] )
)

Value Yr = 
CALCULATE ( SUM ( Table1[Value] ), ALLEXCEPT ( Table1, Table1[Year] ) )

[GilbertQ]

Awesome, thanks for letting me know.