Forum Discussion

Annemie19's avatar
Annemie19
Helper II
5 years ago

Get latest date for certain task ID

Hi, 

 

I have the data as shown below:

 

Entity IdCovenant IDCovenant Sub TypeTaskIDLatest Comment Date
21632312108Net Interest Cover3849911/11/2020
21632312108Net Interest Cover3849902/11/2020
21632312109Leverage Ratio3850811/11/2020
21632312109Leverage Ratio3850802/11/2020
21632312110Capital Expenditure3851702/11/2020
21632312110Capital Expenditure3851711/11/2020

 

for each covenant ID and task ID I only want to show the last 'Latest Comment Date'. 

For instance if you look at Task ID 38499 I only want to see the 'Latest Comment Date' as 02/11/2020

 

What measure can I write for this?

 

Kind Re

Date
groupby
measure

3 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    Annemie19 , Create a measure like

     

    Last Date = calculate(max(Table[Latest Comment Date]), filter(Table, Table[Latest Comment Date] = calculate(max(Table[ Latest Comment Date]), allexcept(Table, Table[TaskID]))) )

  • Annemie19's avatar
    Annemie19
    Helper II
    5 years ago

    amitchandak, Thanks for the help. 

     

    However, this is the measure I used. 

     

    LAST DATE =  calculate(max(VwCovenant[CovenantCommentsTimestamp]), filter(VwCovenant, VwCovenant[CovenantCommentsTimestamp] = calculate(max(VwCovenant[CovenantCommentsTimestamp]), allexcept(VwCovenant, VwCovenant[TaskID]))) )
     
    And it is returning a blank column
    Entity IdCovenant IDCovenant TypeCovenant Sub TypeTaskIDDays OverdueLatest CommentCovenantCommentsTimestampLAST DATE
    21632312108Financial CovenantNet Interest Cover38500-26Covenant returned for amendments. Reason: Capture as Net interest cover, covenant sub type .11/11/2020 
    21632312108Financial CovenantNet Interest Cover38500-26Covenant returned for amendments. Reason: Quarterly except FYE(march) .02/11/2020 
    21632312108Financial CovenantNet Interest Cover38501-208Covenant returned for amendments. Reason: Capture as Net interest cover, covenant sub type .11/11/2020 
    21632312108Financial CovenantNet Interest Cover38501-208Covenant returned for amendments. Reason: Quarterly except FYE(march) .02/11/2020 
  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Same problem with me I have project and each project have multiple task and its task completion date I want to show latest task based on latest date in power bi could any one help me