Milestone

[Anonymous]

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

[Anonymous]

Hi @Anonymous ,

Did the above solution solve your problem?

Best Regards,

Jay

[MFelix]

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.