Forum Discussion
Add a same value across selected time period
Hi,
I have a per day project cost for different organization. This project has a start date and an end date. I found out how to visualize the per- day project cost across its course of time (i.e. from start date to end date) by creating a measure:
Amount = CALCULATE(SUM('Table1'[Per Day Cost]),FILTER('Table1','Table1'[Contract start date]<=MAX('Calendar'[Date]) && 'Table1'[Contract end date]>=MAX('Calendar'[Date])))
But I am unable to add its cost over its course.
For example:
If project has per-day cost 5000 from 1/1/2016 to 12/30/2016, it shows a constant line of $5000 between those dates.
In the figure below
Now, I want to calculate a cumulative cost incurred by a project in a time period selected by me from the calendar. For example:
The project A will have a total cost of 5000*30=150,000 from 1/1/2016 to 1/30/2016. Is there any way that I can do that in power BI? Is there a possibility that we can create a sum measure over an existing measure?
I have attached the .pbix file for your reference:
https://drive.google.com/drive/folders/1WTPYJCLYDjJcoaf8dr8q4gPAk1X5H22w?ogsrc=32
6 Replies
- Greg_DecklerCommunity Champion
Yes you can do that. Did you try using the Running Total Quick Measure?
Also, check out my Time Intelligence The Hard Way Quick Measure here:
Basically, you just need to create a temp table that FILTER's your table to all rows with a date less than the current context and then do a SUMX on it.
- SaurabhGuptaFrequent Visitor
Hi Greg,
Thanks for the reply. I tried using the new quick measure but it still does not add up the values over the range of selected dates/months/years.
- AnonymousNot applicable
SaurabhGupta,
Could you please re-share the PBIX file? I don't have access to the link you post.
Regards,
Lydia
- SaurabhGuptaFrequent Visitor
Could anyone please help me in solving this problem?
- AnonymousNot applicable
SaurabhGupta,
Please check if the DAX in attached PBIX file return your expected result.
Regards,
Lydia