cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mbegg Member
Member

Create column of daily values summed across a month

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?

 

 Values.JPG

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Create column of daily values summed across a month

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


Did I answer your question? Mark my post as a solution!

"Proud to be a Datanaut!"


Power BI Blog
4 REPLIES 4
Super User
Super User

Re: Create column of daily values summed across a month

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


Did I answer your question? Mark my post as a solution!

"Proud to be a Datanaut!"


Power BI Blog
mbegg Member
Member

Re: Create column of daily values summed across a month

Hi @GilbertQ, thank you, that works

 

 

Super User
Super User

Re: Create column of daily values summed across a month

Awesome, thanks for letting me know.


Did I answer your question? Mark my post as a solution!

"Proud to be a Datanaut!"


Power BI Blog
Community Support Team
Community Support Team

Re: Create column of daily values summed across a month

@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] ) )
Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.