Building a Daily Summary table

[graemejohnson]

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.

[az38]

@graemejohnson 

why do not you just add to table visual 2 fields:

Date

Original Estimate aggregated as SUM

?

[graemejohnson]

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

[az38]

@graemejohnson 

what is the [Sum (Original Estimate)] column?

give a statement if it is a calculated column

[graemejohnson]

It's a measure....

Sum (Original Estimate) = SUM('Original Estimate History'[OriginalEstimate])

So I can make that less obscure by re-writing as follows....but the end result is the same 

[az38]

@graemejohnson 

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])
		)
	)
)

[graemejohnson]

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!

[az38 · Accepted solution]

@graemejohnson 

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

[graemejohnson]

😀OMG!  You are a legend!

Thank you so, so much for taking time to help me!

[graemejohnson]

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

[az38]

@graemejohnson 

measure in your Calendar table could look smth like

Remaining Estimated Hours As At Date = 
var _date = CALCULATE(MAX('Remaining Estimate Changes'[DateModified]),  <= 'Remaining Estimate Changes'[DateModified] <= SELECTEDVALUE('Planning Calendar'[Date]) )

RETURN
CALCULATE(MAX('Remaining Estimate Changes'[Sum RemainingEstimatedHours]), 'Remaining Estimate Changes'[DateModified] = _date)

but it should be checked on your data model

[graemejohnson]

@az38 

Thanks for your reply...I've applied that Measure but it is giving an error.....

[az38]

@graemejohnson 

try MAXX()

Remaining Estimated Hours As At Date = 
var _date = CALCULATE(MAX('Remaining Estimate Changes'[DateModified]),  <= 'Remaining Estimate Changes'[DateModified] <= SELECTEDVALUE('Planning Calendar'[Date]) )

RETURN
CALCULATE(MAXX('Remaining Estimate Changes', 'Remaining Estimate Changes'[Sum RemainingEstimatedHours]), 'Remaining Estimate Changes'[DateModified] = _date)

[graemejohnson]

I first get this...

Or (when I remove the <=) this...

[az38]

@graemejohnson 

sorry, it takes a long time as i have no possibility to debug by myself

Remaining Estimated Hours As At Date = 
var _date = CALCULATE(MAX('Remaining Estimate Changes'[DateModified]),  FILTER(ALL('Remaining Estimate Changes'), 'Remaining Estimate Changes'[DateModified] <= SELECTEDVALUE('Planning Calendar'[Date])) )

RETURN
CALCULATE(MAXX('Remaining Estimate Changes', 'Remaining Estimate Changes'[Sum RemainingEstimatedHours]), 'Remaining Estimate Changes'[DateModified] = _date)

[graemejohnson]

Thanks, it compiles without error but the results are not what I'd expect....

1) shows the new measure

[az38]

@graemejohnson 

try SUMX() not MAXX 

if it won't help, please, share your pbix-file

[graemejohnson]

Hi

I didn't work - will try and upload my .pbix