- Power BI forums
- Get Help with Power BI
- Desktop
- Service
- Report Server
- Power Query
- Mobile Apps
- Developer
- DAX Commands and Tips
- Custom Visuals Development Discussion
- Health and Life Sciences
- Power BI Spanish forums
- Translated Spanish Desktop
- Training and Consulting
- Instructor Led Training
- Dashboard in a Day for Women, by Women
- Galleries
- Community Connections & How-To Videos
- COVID-19 Data Stories Gallery
- Themes Gallery
- Data Stories Gallery
- R Script Showcase
- Webinars and Video Gallery
- Quick Measures Gallery
- 2021 MSBizAppsSummit Gallery
- 2020 MSBizAppsSummit Gallery
- 2019 MSBizAppsSummit Gallery
- Events
- Ideas
- Custom Visuals Ideas
- Issues
- Issues
- Events
- Upcoming Events
- Community Blog
- Power BI Community Blog
- Power BI 中文博客
- Community Support
- Community Accounts & Registration
- Using the Community
- Community Feedback

Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

- Power BI forums
- Forums
- Get Help with Power BI
- Desktop
- Re: How to sum values by period based on the start...

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

How to sum values by period based on the start and end (may cross multiple periods)

03-08-2016
08:37 PM

Hi everyone,

I'm using Power BI desktop working on a reporting task, and have been struggling with the following problem for days. I'm quite new to Power BI and have very basic DAX knowledge.

Here are the tables (relevant columns) I'd like to use to do the calculation:

**Project:**

**ID, StartDate, EndDate**

E.g.

P00000, 01/01/2016, 31/03/2016, $1200, 3, $400, WIP

P11111, 02/02/2016, 28/02/2016, $100, 1, $100, WIP

P222222, 01/02/2016, 15/03/2016, $400, 2, $200, Unsolicited

**PRPlan:**

**ID, AddedValue, Duration(Month), AddedValue/Month, Type**

E.g.

P00000, $1200, 3, $400, WIP

P11111,$100, 1, $100, WIP

P222222, 400, 2, $200, Unsolicited

**The relationship between Project and PRPlan is one-one.**

**Date:**

**Period, Date, Day, Month, Quarter, Year**

E.g.

Jan 2016, 01/01/2016, 1, 1, 1, 2016

Feb 2016, 01/02/2016, 1, 2, 1, 2016

Mar 2016, 01/03/2016, 1, 3, 1, 2016

**This is a new table created using DateTable = CALENDAR (MINX(PR, PR[StartDate]), MAXX(PR,PR[EndDate]))**

I'd like to sum the AddedValue/Month by Period and Type (add up AddedValue/Month of all applicable projects together, that is of a specific type that occurs in a specific period), so the end result should look like this based on the sample data:

Period, WIP, Unsolicited

E.g.

Jan 2016, $400, 0

Feb 2016, $500, $200

Mar 2016, $400, $200

I've tried to add a new column using sumx which returns nothing:

WIP = **SUMX(filter(PRPlan, PRPlan[Type] = "WIP"), IF(AND(RELATED(PR[StartDate]) <= DateTable[Date], RELATED(PR[EndDate]) >= DateTable[Date]), SUM(RPPlan[Added Value/Month])))**

** **

I've tried CALCULATE(SUM(...), FILTER(...)) as well using similar logic, and I guess the major concern here is that these two tables do not have direct relationship.

Could anyone please give me any hint how to achieve this?

Thanks a lot in advance,

Olivia

Solved! Go to Solution.

2 ACCEPTED SOLUTIONS

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

03-09-2016
03:52 AM

@starmoonknight For simplicity ( also is easier & better to use ) I used only one table 'Projects'..You can use RELATED() to bring all data to one table or Merge them with powerquery before load.

Projects table

ID Start End AddedValue/Month Type 1 01/01/2016 31/03/2016 400 WIP 2 02/02/2016 28/02/2016 100 WIP 3 01/02/2016 15/03/2016 200 Unsolicited

Importand: You must not have a relationship between Dates table & Project..

Create this measure that sums the monthly added value for every month/period that the project start & end date are between the selected period.

ValueAdded = CALCULATE ( SUM( Project[AddedValue/Month] ); FILTER ( Project; Project[Start] <= CALCULATE ( MAX ( Dates[Date]) ) ); FILTER ( Project; Project[End] >= CALCULATE ( MIN ( Dates[Date] ) ) ) )

I add the result in a matrix table with Dates[Period] on rows and as column add Project[Type]

Please note that this will sum projects even if they have even on day in a month..

For more on the subject check this great post by Gerhard Brueckl

http://blog.gbrueckl.at/2014/12/events-in-progress-for-time-periods-in-dax/

Konstantinos Ioannou

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

03-16-2016
05:52 AM

@starmoonknight The reason is that based on the structure of your data the formula calculates the Mothly Added Value which when you have a month in row ( also filter context ) it sums for every project that is active the monthly sum. Which is ok. But for the total row it also calculates correct the Monthly Added value for all projects since there is no filter on dates, that means that results to the monthly added value for all projects and not the sum of all months.

Not sure if I explained well enough

example

ID Start End AddedValue/Month Type 1 01/01/2016 31/03/2016 400 WIP 2 02/02/2016 28/02/2016 100 WIP 3 01/02/2016 15/03/2016 200 Unsolicited

Assume we have the table above the formula if there is a a month filter i.e.

For January it sums only Project 1 since this is active. = 400

For Februaryit sums Project 1 & 2 & 3 . = 700

For March it sums Project 1 & 3 . = 600

But for total there is no month filter and it sums all the column = 700

So it is working correct.

You can try the formula SUMX() that will sum all the month values

ValueAddedTotals = SUMX(VALUES(DateTable[Months),[AV])

Which is actually if we extend formulas

AVTotals = SUMX ( VALUES ( DateTable[Months] ); CALCULATE ( SUM ( RPPlan[Added Value/Month] ); FILTER ( RPPlan; RPPlan[start] <= CALCULATE ( MAX ( DateTable[Date] ) ) ); FILTER ( RPPlan; RPPlan[end] >= CALCULATE ( MIN ( DateTable[Date] ) ) ) ) )

Performance will not be good...If I find time I will try to come up with a faster one

Hope that helps and you understand the concept.

Konstantinos Ioannou

16 REPLIES 16

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

05-04-2023
05:29 AM

But I didn't work for me.

ID Start End AddedValue/Month Type 1 01/01/2016 31/03/2016 400 WIP 2 02/02/2016 28/02/2016 100 WIP 3 01/02/2016 15/03/2016 200 Unsolicited

iwil get jan 400

feb 300

and nothing for march. i used

SUMX ( VALUES ( DateTable[Months] ); CALCULATE ( SUM ( RPPlan[Added Value/Month] ); FILTER ( RPPlan; RPPlan[start] <= CALCULATE ( MAX ( DateTable[Date] ) ) ); FILTER ( RPPlan; RPPlan[end] >= CALCULATE ( MIN ( DateTable[Date] ) ) ) ) )

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

11-23-2019
10:47 AM

Dear Community,

I want to create a simular table but then in Power Query using the m Query language.

Could someone help me with this?

When I use the DAX code in Power BI Desktop, my Desktop file beocomes very slowly:

Running total size =

CALCULATE (

COUNTA (Data [StaffLevelName]),

FILTER ( ALL (Data) , Data [FiscalStartYear] <= MAX (Data [FiscalStartYear]))

)

Thank you very much.

Kind regards Louis van Paassen

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

03-09-2016
03:52 AM

@starmoonknight For simplicity ( also is easier & better to use ) I used only one table 'Projects'..You can use RELATED() to bring all data to one table or Merge them with powerquery before load.

Projects table

ID Start End AddedValue/Month Type 1 01/01/2016 31/03/2016 400 WIP 2 02/02/2016 28/02/2016 100 WIP 3 01/02/2016 15/03/2016 200 Unsolicited

Importand: You must not have a relationship between Dates table & Project..

Create this measure that sums the monthly added value for every month/period that the project start & end date are between the selected period.

ValueAdded = CALCULATE ( SUM( Project[AddedValue/Month] ); FILTER ( Project; Project[Start] <= CALCULATE ( MAX ( Dates[Date]) ) ); FILTER ( Project; Project[End] >= CALCULATE ( MIN ( Dates[Date] ) ) ) )

I add the result in a matrix table with Dates[Period] on rows and as column add Project[Type]

Please note that this will sum projects even if they have even on day in a month..

For more on the subject check this great post by Gerhard Brueckl

http://blog.gbrueckl.at/2014/12/events-in-progress-for-time-periods-in-dax/

Konstantinos Ioannou

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

06-20-2020
03:57 PM

I tried but the formula didn't work

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

06-20-2020
04:04 PM

the error wa a little parenthesis in wrong place

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

03-09-2016
02:04 PM

Thanks a lot Konstantinos,

I've tried you way, and it worked! Really appreciate that!

I once made a mistake that I created a new column rather than a new measure, and it turned out than new column with the same formula stilled caused a relationship not identified error. Just wondering how can I tell whether I should create a new measure or a new column when it comes to some calculated/aggregated values.

By the way, I've got another table, Prospects, which has three relevant fields (start, end, addedValue/Month) and is not related to any other table, but the manager would like to sum the AddedValue together with other projects, and show the Type as "Expected". I guess I might need to merge Prospects and Project?

Anyway, I'll have some exploration myself, and thank you so much again,

Olivia

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

03-10-2016
06:08 AM

@starmoonknight You are welcome.

You need to create the same exact measure for the Prospects table and then create a new measure that is the sum of Projects & Prospects addedvalue

Expected added value = [AddedValue] + [ProspectsAddedValue]

It will work fine with fields from date table like months etc. You can try it

edit: You almost always need a relationship for when you need to filter with values from other tables except when this exact problem occurs ( Event in progress ) when you need a calculation for active sum, count between 2 dates.

Konstantinos Ioannou

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

03-15-2016
09:41 PM

Hi @konstantinos,

Sorry to bother you again, but I just noticed that the Total auto-calculated by the Matrix is not the sum of Added Values of all the periods, could you please suggest any possible reasons?

Here is my formula for AV

AV =

CALCULATE(

SUM(RPPlan[Added Value/Month]),

FILTER(

RPPlan,

RPPlan[start]<= CALCULATE(MAX( DateTable[Date]))

),

FILTER(

RPPlan,

RPPlan[end] >= CALCULATE(MIN(DateTable[Date]))

)

)

I've used filters to play around, and it seems to display the latest value as Total for most cases or higher than the latest value with a little variance.

I've tries the other two ways mentioned in the blog you've shared; however, neither did not work in my case (can only show one dimension and get an error if try to add a column, and have exactly the same value for all the periods which is the Total) and gave different results.

AV~1 =

CALCULATE (

SUM(RPPlan[Added Value/Month]),

GENERATE (

VALUES ( DateTable[Date] ),

FILTER (

RPPlan,

CONTAINS (

DATESBETWEEN (

DateTable[Date],

RPPlan[start],

RPPlan[end]

),

[Date], DateTable[Date]

)

)

)

)

AV~2 =

CALCULATE(

SUM(RPPlan[Added Value/Month]),

FILTER(

GENERATE(

SUMMARIZE(

RPPlan,

RPPlan[start],

RPPlan[end]

),

DATESBETWEEN(

DateTable[Date],

RPPlan[start],

RPPlan[end]

)

),

CONTAINS( VALUES(DateTable[Date]),[date], DateTable[Date])

)

)

I used a single card to show the Added Value as well, again, it gives me a wrong number (which is consistent with the Total in the Matrix)

It would be really appreciated if you could give me some hint of the logic of the Total calculated here.

Cheers,

Olivia

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

03-16-2016
05:52 AM

@starmoonknight The reason is that based on the structure of your data the formula calculates the Mothly Added Value which when you have a month in row ( also filter context ) it sums for every project that is active the monthly sum. Which is ok. But for the total row it also calculates correct the Monthly Added value for all projects since there is no filter on dates, that means that results to the monthly added value for all projects and not the sum of all months.

Not sure if I explained well enough

example

Assume we have the table above the formula if there is a a month filter i.e.

For January it sums only Project 1 since this is active. = 400

For Februaryit sums Project 1 & 2 & 3 . = 700

For March it sums Project 1 & 3 . = 600

But for total there is no month filter and it sums all the column = 700

So it is working correct.

You can try the formula SUMX() that will sum all the month values

ValueAddedTotals = SUMX(VALUES(DateTable[Months),[AV])

Which is actually if we extend formulas

AVTotals = SUMX ( VALUES ( DateTable[Months] ); CALCULATE ( SUM ( RPPlan[Added Value/Month] ); FILTER ( RPPlan; RPPlan[start] <= CALCULATE ( MAX ( DateTable[Date] ) ) ); FILTER ( RPPlan; RPPlan[end] >= CALCULATE ( MIN ( DateTable[Date] ) ) ) ) )

Performance will not be good...If I find time I will try to come up with a faster one

Hope that helps and you understand the concept.

Konstantinos Ioannou

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

05-04-2023
05:35 AM

Hi, It didn't work for me. I don't get 600 for march if I use the dax

SUMX ( VALUES ( DateTable[Months] ); CALCULATE ( SUM ( RPPlan[Added Value/Month] ); FILTER ( RPPlan; RPPlan[start] <= CALCULATE ( MAX ( DateTable[Date] ) ) ); FILTER ( RPPlan; RPPlan[end] >= CALCULATE ( MIN ( DateTable[Date] ) ) ) ) )

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

07-04-2018
07:27 PM

Thanks @konstantinos this really helped me!

As you already mentioned performance is not good. Did you find time to work your brilliantness and find a faster way?

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

03-07-2018
12:51 PM

Love this, was wondering if you every came up with a more efficient and faster way of doing this, i have millions of records that i need to cal WIP for.

Thanks

Josh

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

09-08-2017
05:29 AM

Thanks @konstantinos your measure helped me to figure out how to change my calculations into right direction!

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

05-25-2017
12:43 PM

Hi I have similar calculation like this:

1. Main date 2.sub date and amount

i wants to calcualte the sum of subdate which is grater than todays date...

somehow it is showing blank

thanks

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

03-16-2016
03:06 PM

Hi @konstantinos,

Your explaination is clear and crystal and I understand what PBI is trying to do with the Total immediately after going through your example.

I'll try to get rid of the automaticaly calculated Total which could be a little confusing to managers and implement the customised total.

Thank you so much for the help!

Cheers,

Olivia

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

03-10-2016
06:41 PM

Thanks again, Konstantinos.

I've successfully completed the task, in a slightly differnt way. Anyway, thank you so much, your reply really helped a lot!

Announcements

Check out the October 2024 Power BI update to learn about new features.

Learn from experts, get hands-on experience, and win awesome prizes.

Featured Topics

Top Solution Authors

User | Count |
---|---|

108 | |

108 | |

107 | |

89 | |

61 |

Top Kudoed Authors

User | Count |
---|---|

171 | |

139 | |

133 | |

103 | |

86 |