topic SUM of value by customer and month in DAX Commands and Tips

SUM of value by customer and month

Anonymous — Fri, 24 Apr 2020 15:05:59 GMT

I hope someone can help!

I am trying to create a calculated column or measure that will track the cumulative total of customers subscription values by month

IE Total of Customer As $ IN January, Total of Customers Bs $ In January, Total of Cusotomer As $ in February, etc

AND

Total for January, Total for February, etc

Any help greatly appreciated 🙂

i currently have the data in a table similar to the below.

Customer	Subsctiption ID	Consumption Date	Value in $
Customer A	1	01/01/2019	45
Customer A	2	01/01/2019	20
Customer A	3	01/01/2019	15
Customer A	4	01/01/2019	75
Customer B	5	01/01/2019	78
Customer B	6	01/01/2019	15
Customer B	7	01/01/2019	15
Customer C	8	01/01/2019	463
Customer C	9	01/01/2019	45
Customer A	1	01/02/2019	499
Customer A	2	01/02/2019	42
Customer A	3	01/02/2019	12
Customer A	4	01/02/2019	2
Customer B	5	01/02/2019	85
Customer B	6	01/02/2019	0
Customer B	7	01/02/2019	83
Customer C	8	01/02/2019	846
Customer C	9	01/02/2019	96

Re: SUM of value by customer and month

Greg_Deckler — Fri, 24 Apr 2020 16:09:06 GMT

What is the expected output from your sample data?

Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

Re: SUM of value by customer and month

ash123 — Sat, 25 Apr 2020 11:30:52 GMT

Hi Rob,

I tried to solve your query not sure if that exactly matches your requirement.

I added a calculated Column Commsales. See the table below

Dax to achieve this:

Commsales =

VAR Sales =

SUM ( 'Table'[Value in $] )

VAR customer = 'Table'[Customer]

VAR C_date = 'Table'[Consumption Date]

VAR Subscription = 'Table'[Subsctiption ID]

RETURN

CALCULATE (

SUM ( 'Table'[Value in $] ),

FILTER (

'Table',

'Table'[Customer] = customer

&& 'Table'[Consumption Date] = C_date

&& 'Table'[Subsctiption ID] <= Subscription))

Visuals:

Regards,

Kaushlendra Mishra

www.powerbitalks.com

Linkedin- www.linkedin.com/in/kaushlendra-mishra-49627310

Re: SUM of value by customer and month

Anonymous — Sun, 26 Apr 2020 17:33:10 GMT

Hi Anonymous ,

Incase this is what you are looking for, you will need to create a Calendar Table using CalendarAuto() and link it with you consumption date.

Drag Month and Year from the Calendar and Customer and Value in $ from the Subscriber Table.

Regards,

Harsh Nathani

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

In case it did not help, please provide additional information and mark me with @ so that I can investigate further

Re: SUM of value by customer and month

Anonymous — Sun, 16 Aug 2020 13:27:49 GMT

Hi Anonymous ,

Did the solution work for you.

Please mark as solution for other community members to benefit.

Regards,
Harsh Nathani
Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)