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?
Go to Solution.
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
Hi @GilbertQ, thank you, that works
Awesome, thanks for letting me know.
You could also add calculated columns as shown below.
Value Mth =
SUM ( Table1[Value] ),
ALLEXCEPT ( Table1, Table1[Year], Table1[MonthNo] )
Value Yr =
CALCULATE ( SUM ( Table1[Value] ), ALLEXCEPT ( Table1, Table1[Year] ) )
Engage and empower students with Power BI!
Continue your learning in our online communities.
Travel to Melbourne and network with thousands of peers!
Check out what's new in the Power BI Community!