Forum Discussion
Getting average profit during different time periods
I have a dataset that follows this general structure: We have data on numerous business (clients) and their sales/profit trends over various months.
Client_Sales
| Client | MonthYear | Sales | Profit |
| ABC | 01/01/2022 | 800 | 1000 |
| ABC | 02/01/2022 | 900 | 1200 |
| ABC | 03/01/2022 | 450 | 800 |
| ABC | 04/01/2022 | 239 | 1239 |
| ABC | 05/01/2022 | 1231 | 12312 |
| ABC | 06/01/2022 | 400 | 231 |
| ABC | 07/01/2022 | 4500 | 10020 |
| ABC | 08/01/2022 | 450 | 230 |
| ABC | 09/01/2022 | 290 | 1234 |
| ABC | 10/01/2022 | 76 | 300 |
| ABC | 11/01/2022 | 89 | 1230 |
| ABC | 12/01/2022 | 210 | 4500 |
I have a second table that looks like this: All of the business in the the above table were recently 'sold' or 'changed management'.
Client_Acquisition:
| Client | Date of Sale |
| ABC | 05/15/2022 |
- I would like to see how profits were affected before during and after the sale of the business. Specifically, I want to create a line graph that shows what the profit was:
- 12 months rolling profit ending 3 months prior to the date sale. In this case, the sale date was 5/15/2022. So I want average 12 month rolling data up until 02/01/2022.
- What profits were the month of the sale. In this case the full month of May 2022.
- 3 months post sale. In this case it would be total average profit for June, July, and August 2022.
- Month by month after that time point. September, October, November, and December 2022 also as separate points.
Any help on how to create a function or equation to do this would be immensely helpful.
1 Reply
- amitchandak
Super User
allora , try like , with help from date table
12 Month Avg = CALCULATE(AverageX(Values('Date'[MONTH Year]),calculate(Sum('Table'[profilt])))
,DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-12,MONTH))3 Month Avg = CALCULATE(AverageX(Values('Date'[MONTH Year]),calculate(Sum('Table'[profilt])))
,DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),3,MONTH))Rolling Months Formula: https://youtu.be/GS5O4G81fww
You can consider Window function too
Power BI Window function Rolling, Cumulative/Running Total, WTD, MTD, QTD, YTD, FYTD: https://youtu.be/nxc_IWl-tTc
Rolling 12 = CALCULATE(AverageX(Values('Date'[MONTH Year]),calculate(Sum('Table'[profilt]))), WINDOW(-11,REL, 0, REL, ADDCOLUMNS(ALLSELECTED('Date'[Month Year],'Date'[Month Year Sort] ),ORDERBY([Month Year Sort],asc)))
