cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Joost
Regular Visitor

Accumulated values with calendar

Hi,

 

I am having a slight problem and was hoping someone could help me.

I want to display the costs of projects so far along with the time they occured in. The projects have a start and end date. Thus, I made a calendar table:

 

Calendar = CALENDAR(MIN('P2Project-FULL'[Start date]),DATEVALUE("31-May-2017"))

 

I then calculate the costs with the following measure:

Actuals period = CALCULATE(SUM('P2Project-FULL'[Costs Actual]),FILTER('P2Project-FULL','P2Project-FULL'[Start date] <= MAX('Calendar'[Date])))

The total amount is how I want to have it as it coressponds with the total amount of the colum [Costs Actual] from the table P2Project-Full. 

 

The problem I am getting however is that when I put them in a line and clustered chart, the amounts of each year add up to the total (in 2017). Let me show you what I mean with a picture:

1.png

 

 As you can see the values are accumalating for the green bars, as now for instance 2016 = 2016 + 2015 + 2014. 

What I want is that the year only counts for that year, so 2016 only counts the values of 2016 etc. 

 

Can anyone help me fix this? 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Super User I
Super User I

Re: Accumulated values with calendar

@Joost i could be wrong but it looks like you linking to the max date in your calendar which might not work, since you need it to take start and end date of the project correct?

 

a few questions 

1. do you have a date table

2. what date in the date in the date table linked to in your data?

 

post pic of your relationship view please 

 


Did I answer your question? Mark my post as a solution.
Proud a to be a Datanaut!

View solution in original post

4 REPLIES 4
Super User I
Super User I

Re: Accumulated values with calendar

@Joost

 

your calculate is a cummulative figure  ie. 

P2Project-FULL'[Start date] <= MAX('Calendar'[Date])))

 from you will need to get the correct end date to not cummulate


Did I answer your question? Mark my post as a solution.
Proud a to be a Datanaut!
Joost
Regular Visitor

Re: Accumulated values with calendar

@vanessafvg Could you elaborate? I don't really understand what you mean.

Super User I
Super User I

Re: Accumulated values with calendar

@Joost i could be wrong but it looks like you linking to the max date in your calendar which might not work, since you need it to take start and end date of the project correct?

 

a few questions 

1. do you have a date table

2. what date in the date in the date table linked to in your data?

 

post pic of your relationship view please 

 


Did I answer your question? Mark my post as a solution.
Proud a to be a Datanaut!

View solution in original post

Joost
Regular Visitor

Re: Accumulated values with calendar

Thank you very much, I completely missed the relationship between the dates of the two tables, oops. 

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors