Dates rolled up to Project level

[chrismcdonald]

Hello,

I am looking for help again please.

I am trying to create a view table that shows a roll up of dates to a project level for the Construction Complete activity

Example Data

Project #	Sub Project #	Activity	Forecast	Actual
1234	1	Construction Complete	15/02/2020	17/02/2020
1234	2	Construction Complete	10/02/2020

Expected Result

Project #	Activity	Forecast	Actual
1234	Construction Complete	15/02/2020

what I am after is 

Forecast - to always show the latest date (working fine using the summarize Latest date however might be a better solution)

Actual - To always show the latest date or Blank of both sub projects are not complete

Any help would be amazing.

Thank you

[v-eachen-msft]

Hi @chrismcdonald ,

You could create two measures to repalce "Forcast" and "Actual".

Forcast1 =
CALCULATE ( MAX ( 'Table'[Forecast] ), FILTER ( 'Table', 'Table'[Project #] ) )

Actual1 =
IF (
    COUNTROWS (
        FILTER ( ALLEXCEPT ( 'Table', 'Table'[Project #] ), 'Table'[Actual] = BLANK () )
    ) > 0,
    BLANK (),
    CALCULATE ( MAX ( 'Table'[Actual] ), ALLEXCEPT ( 'Table', 'Table'[Project #] ) )
)

Here is the result.

[chrismcdonald]

@v-eachen-msft this is very close to what I was looking for however it doesn't do exactly what I need and probably because I didn't give you enough of the details.

When I have more than 1 project number the actuals only populate when all projects have an actual however I was looking to have the "summarization" for lack of a better term to be on a per project basis. It does populate the correct actuals for both projects ones all projects have an actual.

I feel you got me really close and would really appreciate the help getting the last bit.

I have uploaded the example files to Google Drive to try and make it a littles easier. https://drive.google.com/drive/u/0/folders/1x4l2GzRDqQ1SjpKa81fqSjE0Ap3cl2ze

[v-eachen-msft · Accepted solution]

Hi @chrismcdonald ,

Is the following image your expected result?

You need to edit the measure "Actual1".

Actual1 =
IF (
    CALCULATE (
        COUNTBLANK ( 'Table'[Actual] ),
        ALLEXCEPT ( 'Table', 'Table'[Project #] )
    ) > 0,
    BLANK (),
    CALCULATE ( MAX ( 'Table'[Actual] ), ALLEXCEPT ( 'Table', 'Table'[Project #] ) )
)

[chrismcdonald]

That is perfect.. Thank you so much for your assistance.

I had been googling this one for days and couldn't find the solution.

Thank you again.!!

[amitchandak]

@chrismcdonald 

use the measure in table, it should filter

Measure = 
VAR __id = MAX ( 'Table'[Project #] )
VAR __date = CALCULATE ( MAX( 'Table'[Forecast] ), ALLSELECTED ( 'Table' ),  'Table'[Project #] = __id ) 
RETURN CALCULATE ( count ( 'Table'[Project #] ), VALUES ( 'Table'[Project # ), 'Table'[Project #] = __id, 'Table'[Forecast] = __date )