Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Milestone

Hi Community,

 

the following table shows mky exemplary data.

 
 

 

I want to use the Milestone_Planned Start and Milestone_Planned End data to calculate my current milestone.

 

The following table shows the logic based on the data.
A milestone is considered to have been reached when the end date is entered.

it is possible to calculate such a thing in Dax

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Need Help

2 Replies

  • MFelix's avatar
    MFelix
    Super User
    5 years ago

    Hi Anonymous ,

     

    I would do some transformations in Power query and then add two calculated measures:

     

    Pwer Query:

    • Select the column project and other that are needed and Unpivoted other columns
    • Get two columns Attibute (Milestone Star end) and Value (Dates)
    • Split column Attribute by delimeter
    • You will get two new column Attribute1 (Milestone) Attibute 2 (Start End)
    • Pivot column Attribute2 with the values of the dates
    • Rename columns
    • Result below:

     

    Add the following measures:

    Next Milestone =
VAR temp_table =
    FILTER (
        SUMMARIZE ( 'Table'; 'Table'[Project]; 'Table'[Milestone]; 'Table'[End] );
        'Table'[End] = BLANK ()
    )
RETURN
    MINX ( temp_table; 'Table'[Milestone] )


Milestone current =
VAR temp_table =
    FILTER (
        SUMMARIZE ( 'Table'; 'Table'[Project]; 'Table'[Milestone]; 'Table'[End] );
        'Table'[End] <> BLANK ()
    )
RETURN
    MAXX ( temp_table; 'Table'[Milestone] )

     

    final result below and in attach PBIX file.

    Be aware that I'm assuming that your milestone are somehow identified as 1, 2, 3 if not you need to have some whay of sorting them in your model.

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi Anonymous ,

     

    Did the above solution solve your problem?

     

    Best Regards,

    Jay