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

     

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!

       

      • az38's avatar
        az38
        Community Champion
        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