Forum Discussion

chrismcdonald's avatar
chrismcdonald
Helper I
6 years ago
Solved

Dates rolled up to Project level

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 #ActivityForecastActual
12341Construction Complete15/02/202017/02/2020
12342Construction Complete10/02/2020 


Expected Result

Project #ActivityForecastActual
1234Construction Complete15/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

Need Help
  • v-eachen-msft's avatar
    v-eachen-msft
    6 years ago

    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 #] ) )
)

     

5 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    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 )

     

  • v-eachen-msft's avatar
    v-eachen-msft
    Community Support
    6 years ago

    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's avatar
      chrismcdonald
      Helper I
      6 years ago

      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's avatar
        v-eachen-msft
        Community Support
        6 years ago

        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 #] ) )
)