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

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!

  • graemejohnson's avatar
    graemejohnson
    Helper II
    6 years ago

    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...

     

    DateRemaining Estimated Hours As At Date
    02 March 20200
    ...all dates inbetween0
    17 March 202015
    ...all dates inbetween15
    26 March 2020195
    ...all dates inbetween195
    01 April 2020225
    ...all dates inbetween225
    29 May 2020225

     

     

     
  • az38's avatar
    az38
    Community Champion
    6 years ago

    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'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