How to sum data from certain month of multiple years?

[Jennie_KK]

Hey Everyone,

I am new to PowerBI.

Now I have the data like followings in Table [Data], I also have a [Date] table

Date	Category	Value
01-08-2021	1	7
01-07-2022	1	9
01-08-2021	2	6
01-03-2022	3	3
01-08-2022	5	5

I would like to have sum of for example August of every year, which should be like these:

Date	Sum
01-08-2021	13
01-08-2022	5

Can someone please help me with how to calculate this measurement?

Many Thanks!

[Anonymous]

Hi @Jennie_KK ,

Check this formula:

Measure = 
var current_ = CALCULATE(SUM('Table'[Value]),FILTER(ALLSELECTED('Table'),YEAR('Table'[Date])=YEAR(SELECTEDVALUE('Table'[Date]))&&MONTH('Table'[Date])=MONTH(SELECTEDVALUE('Table'[Date]))))
var previous_ = CALCULATE(SUM('Table'[Value]),FILTER(ALLSELECTED('Table'),YEAR('Table'[Date])=YEAR(SELECTEDVALUE('Table'[Date]))-1&&MONTH('Table'[Date])=MONTH(SELECTEDVALUE('Table'[Date]))))
return
(current_-previous_)/previous_

Best Regards,

Jay

[BA_Pete]

Hi @Anonymous , @Anonymous ,

I've removed the solution mark from this post for the following reasons:

1) The OP hasn't indicated that any solution has worked for them but, given that a working answer was offered 14 days prior to this one, there was no reason to provide a further answer.

2) The provided answer does not promote the generally-accepted best practice of using a calendar table and OTB time intelligence functions.

3) The DAX provided is needlessly complicated for what I assume is a fairly new user, especially in contrast to the simplicity of the OTB time intelligence option. This is not helped by the fact that the code is completely unformatted, thereby making it even more intimidating for a new user.

4) The measure provided resolves to "Infinity" in divide-by-zero scenarios, which clutters up reports and decreases clarity of message.

Happy to discuss if you feel my answer would not work for the OP for any reason.

Pete

[BA_Pete]

Hi @Jennie_KK ,

Create a measure as follows:

_valueSum = SUM(Data[Value])

Put Data[Date] and [_valueSum] into a visual together, and all the same dates will be aggregated together.

Pete

[Jennie_KK]

Hey @BA_Pete 

Thank you very much for your reply.

The real problem here is,

I would like to calculate the growth rate between Aug.2021 and Aug. 2022 like this

Date	Sum	Growth%
01-08-2021	13	0%
01-08-2022	5	(5-13)/13=-61%

Do you maybe have an idea how to do that?

[BA_Pete]

Hi @Jennie_KK ,

Ah, moving goalposts, my favourite kind! 😉

To get the YoY growth rate, you need to set up slightly differently. You'll need to relate your calendar table to your fact table on Date[Date] ONE : MANY Data[Date].

Then create the following measure:

_valueYoYChange% =
VAR __valueSumCY =
SUM(Data[Value])
VAR __valueSumPY =
CALCULATE(
    SUM(Data[Value]),
    SAMEPERIODLASTYEAR(Date[Date])
)
VAR __valueSumChange =
__valueSumCY - __valueSumPY
RETURN
DIVIDE(__valueSumChange, __valueSumPY, 0)

Then use a column from your Date table, and this measure in a visual and all should work as required.

Pete

[BA_Pete]

Hi @Jennie_KK ,

Did this work for you?

Let me know if there's any more info you need to get this working and I'll help you out.

Pete