Forum Discussion

graemejohnson's avatar
graemejohnson
Helper II
6 years ago
Solved

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...
Need Help
  • az38's avatar
    az38
    6 years ago

    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's avatar
graemejohnson
Helper II
6 years ago

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's avatar
az38
Community Champion
6 years ago

graemejohnson 

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

give a statement if it is a calculated column

  • graemejohnson's avatar
    graemejohnson
    Helper II
    6 years ago

    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's avatar
      az38
      Community Champion
      6 years ago

      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's avatar
        graemejohnson
        Helper II
        6 years ago

        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!