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

az38 

 

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

 

az38's avatar
az38
Community Champion
6 years ago

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

    I first get this...

     

     

     

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

     

     
  • az38's avatar
    az38
    Community Champion
    6 years ago

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

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

    1) shows the new measure

     

  • az38's avatar
    az38
    Community Champion
    6 years ago

    graemejohnson 

    try SUMX() not MAXX 

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

  • graemejohnson's avatar
    graemejohnson
    Helper II
    6 years ago

    Hi

     

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