cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
themistoklis
Super User
Super User

Aggregate values by Year-Month

Hello All,

 

Im a fairly new user on PowerBI and i would like to do the following calculation in the application.

 

Example: I have various projects for which I issue multiple invoices to clients (turnover) and I also receive multiple invoices from partners (Cost). The projects can last from 1 month and up to 5 months. This means that these invoices have different issued dates.

 

The first report that i created has the total turnover and cost by month (top table in the image).

 

Now i want to create another report on which i would like to see the state of the projects up to a specific point of time (in this case, month). This means that i need to aggregate the values up to this point of time.

 

In the following image the top table shows the performance of the projects for each one of the months separately and the table at the bottom shows the aggregated information by month.

 

My difficulty is to create the aggregated formula for the calculation on column C, D and E (Cells C9 to E12). One tricky bit is that the aggregation must also work on months which fall on 2 different years.

 

Thanks

 

Tables.JPG

1 ACCEPTED SOLUTION

 

@themistoklis You're welcome 🙂

 

Try this:

Cumulative Turnover = CALCULATE(SUM(Table1[turnovert]),
FILTER(
ALL('Calendar 1'[Date]),
	'Calendar 1'[Date] <= MAX(Table1[Date])))
Cumualtive Cost = CALCULATE(SUM(Table1[Cost]),
FILTER(
ALL('Calendar 1'[Date]),
	'Calendar 1'[Date] <= MAX(Table1[Date])))
Cumulative Other Cost = CALCULATE(SUM(Table1[Other Cost]),
FILTER(
ALL('Calendar 1'[Date]),
	'Calendar 1'[Date] <= MAX(Table1[Date])))

Profit is as before. 

View solution in original post

4 REPLIES 4
KriZo
Helper I
Helper I

@themistoklis

 

Assuming i understood you correctly try this. 

 

First create a Calendar table, if you are so new that this is unfamiliar, let me know. Then create a relationship to your data table. I created a small table for my self, and the initial pivot is like this. 

 

1.PNG

Turned into:

2.PNG

By these formulas:

Cummulative Turnover = CALCULATE(SUM(Table1[turnover]),
FILTER(
ALL('Calendar 1'[Date]),
	'Calendar 1'[Date] <= MAX('Calendar 1'[Date])))
Cummulative Cost = CALCULATE(SUM(Table1[Cost]),
FILTER(
ALL('Calendar 1'[Date]),
	'Calendar 1'[Date] <= MAX('Calendar 1'[Date])))
Cummulative Other Cost = CALCULATE(SUM(Table1[Other Cost]),
FILTER(
ALL('Calendar 1'[Date]),
	'Calendar 1'[Date] <= MAX('Calendar 1'[Date])))
Profit = [Cumulative Turnover]-[Cumulative Cost]-[Cumulative Other Cost]

@KriZo

 

Thanks a lot for your answer. This is nearly what i need...

 

Only one small correction. I forgot to mention that on the initial message, Apologies for that.

 

I want to see all the metrics up untill the date of the last invoicing document.

In your example it is June. So all the calculations are correct up untill June.

After June the values must be zero. If we could ammend the formulas so as to take into account this rule that would be amazing.

 

As for the Calendar table i managed to create it on PowerBI. So im ok with that. That's for offering your help on this.

 

@themistoklis You're welcome 🙂

 

Try this:

Cumulative Turnover = CALCULATE(SUM(Table1[turnovert]),
FILTER(
ALL('Calendar 1'[Date]),
	'Calendar 1'[Date] <= MAX(Table1[Date])))
Cumualtive Cost = CALCULATE(SUM(Table1[Cost]),
FILTER(
ALL('Calendar 1'[Date]),
	'Calendar 1'[Date] <= MAX(Table1[Date])))
Cumulative Other Cost = CALCULATE(SUM(Table1[Other Cost]),
FILTER(
ALL('Calendar 1'[Date]),
	'Calendar 1'[Date] <= MAX(Table1[Date])))

Profit is as before. 

View solution in original post

 

Working great @KriZo

 

Thanks again!

Helpful resources

Announcements
Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Top Solution Authors
Top Kudoed Authors