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

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

       

      • graemejohnson's avatar
        graemejohnson
        Helper II
        6 years ago

        😀OMG!  You are a legend!


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