Forum Discussion
Building a Daily Summary table
Please can anyone help!
I am trying to build a PowerBI report to sit on top of a job estimation system.
I have this table which shows a history of the jobs (IssueID) that have been estimated and the dates on which the estimate was provided.
What I am attempting to do is to build daily summary that shows on any given date, the sum of the most recent (on or before the date in question) OriginalEstimate across all IssueID values e.g.
I've been looking at this for a day and just cannot figure out how to pick out the appropriate values to sum up.
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
17 Replies
why do not you just add to table visual 2 fields:
Date
Original Estimate aggregated as SUM
?
I've possibly misunderstood, but I'm not sure that's what I need.....I was going down the route of creating a summary table with a row for each date, and then it's the calculation of the sum of 'the most recent' estimate per Issue up to that point of time that I just cannot get my head around....on 26th March I need it to pull in a record from 26th and also one from the 25th
what is the [Sum (Original Estimate)] column?
give a statement if it is a calculated column
