Forum Discussion
Building a Daily Summary table
I added a measure into original table 'Original Estimate History'
Measure Sum Estimated = VAR _lastDateTable = ADDCOLUMNS( SUMMARIZE( FILTER( ALL('Original Estimate History'), 'Original Estimate History'[DateModified] <= SELECTEDVALUE('Original Estimate History'[DateModified]) ), 'Original Estimate History'[IssueID], "LastDate", MAX('Original Estimate History'[DateModified]) ), "EstimatedToDate", CALCULATE(MAX('Original Estimate History'[OriginalEstimate]),'Original Estimate History'[IssueID]=EARLIER([IssueID]), 'Original Estimate History'[DateModified]=EARLIER([LastDate]))) RETURN CALCULATE(SUMX(_lastDateTable, [EstimatedToDate]))then created a visual
try
aaa =
SUMMARIZE(
'Original Estimate History',
'Original Estimate History'[DateModified],
"SUM",
CALCULATE(
SUM('Original Estimate History'[OriginalEstimate]),
FILTER(
ALL('Original Estimate History'),
'Original Estimate History'[DateModified]<=EARLIER('Original Estimate History'[DateModified])
)
)
)
Thanks for this......different results but still not what I'm expecting
The values for 25th and 26th look good but the last 2 rows are not what I'd expect...
Really appreciate your (hopefully continued) help!
I added a measure into original table 'Original Estimate History'
Measure Sum Estimated = VAR _lastDateTable = ADDCOLUMNS( SUMMARIZE( FILTER( ALL('Original Estimate History'), 'Original Estimate History'[DateModified] <= SELECTEDVALUE('Original Estimate History'[DateModified]) ), 'Original Estimate History'[IssueID], "LastDate", MAX('Original Estimate History'[DateModified]) ), "EstimatedToDate", CALCULATE(MAX('Original Estimate History'[OriginalEstimate]),'Original Estimate History'[IssueID]=EARLIER([IssueID]), 'Original Estimate History'[DateModified]=EARLIER([LastDate]))) RETURN CALCULATE(SUMX(_lastDateTable, [EstimatedToDate]))then created a visual
😀OMG! You are a legend!
Thank you so, so much for taking time to help me!Hoping you can help me take this further forward az38 !
I now have these overall tables
1) Planning Details - for each item we plan to deliver we have a Planned Start date and Planned end date
2) Planning Calendar - for each row in Planning Details there are rows in this table for each date between Planned Start and Planned End
3) Remaining Estimate Changes - the original table for which the measure you gave me has been applied and which I'm happy is correct 🙂
What I ultimately need to have the Planning Calendar showing the Remaining Estimated Hours per day between Planned Start and Planned End e.g. for HIGHLANDER-3837 which runes from 02 March 2020 thru to 29 May 2020z...
Date Remaining Estimated Hours As At Date 02 March 2020 0 ...all dates inbetween 0 17 March 2020 15 ...all dates inbetween 15 26 March 2020 195 ...all dates inbetween 195 01 April 2020 225 ...all dates inbetween 225 29 May 2020 225