Forum Discussion
Creating calculated columns for monthly totals
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 mon...
v-lili6-msft
Community Support
Community SupportAfter 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
Best Regards,
Lin