Creating calculated columns for monthly totals

[gerget]

Dear all, 

my problem is that I don’t know how I can create columns that contain totals for each month. I need to have columns for:

• Total monthly sales
• Total number of paying clients per month
• Month-to-month sales growth

So my invoice table should look like this (numbers are fictional):

Createdon        totalsales    totalsales per month    ClientID   #Clients/Month   M-M Sales Growth

30.06.2017         10.450                 16.900                    1                    1                        4,1%
31.06.2017         6.450                   16.900                    1                    1                        4,1%
01.07.2017         2.000                   17.550                    3                    3                        3,7%           
01.07.2017         8.000                   17.550                    5                    3                        3,7%
03.07.2017         7.550                   17.550                    7                    3                        3,7%

Also, I can’t create a working relationship between my dates or calendar table and the “createdon” column in the invoices table. When the relationship is active, a dates-totalsales table will only show me a single number, that is the total sum of totalsales and no dates.

I am thankful for any advice! 
Best regards, gerget

[v-lili6-msft]

After my research,you can do these follow my steps like below:

Step 1:

Add two columns:

Month = MONTH(Table4[Createdon ])
Year = YEAR(Table4[Createdon ])

Step 2:

Add Total monthly sales

Column = CALCULATE(SUM(Table4[totalsales]),FILTER(Table4,Table4[Month]=EARLIER(Table4[Month])&&Table4[Year]=EARLIER(Table4[Year])))

Add Total number of paying clients per month

Column 2 = CALCULATE(DISTINCTCOUNT(Table4[ClientID ]),FILTER(Table4,Table4[Month]=EARLIER(Table4[Month])&&Table4[Year]=EARLIER(Table4[Year])))

Add Month-to-month sales growth

Column 3 = IF(Table4[Month]=1,Table4[Column]/CALCULATE(SUM(Table4[totalsales]),FILTER(Table4,12=Table4[Month]&&Table4[Year]+1=EARLIER(Table4[Year]))),Table4[Column]/CALCULATE(SUM(Table4[totalsales]),FILTER(Table4,Table4[Month]+1=EARLIER(Table4[Month])&&Table4[Year]=EARLIER(Table4[Year]))))-1

Result:

Here is demo , please try it

https://www.dropbox.com/s/ufzy60w3ef8dog4/Creating%20calculated%20columns%20for%20monthly%20totals.p...

Best Regards,

Lin