Please help, how to get the latest date out of multiple columns and display the respective column

[Nika777]

Dear BI Community, I just entered the Power BI world and hope that I can find some help for my first steps.

I have a table:

Project	Start Date	Milestone 1	Milestone 2	Milestone 3	Milestone 4
A	1/1/2020	2/15/2020	7/27/2020	8/14/2021	10/23/2022
B	5/3/2020	7/4/2021	8/18/2021	3/10/2022
C	12/12/2020	3/9/2021	8/15/2022

I want to have a gantt view, where BI calculates the latest date of Milestones 1-4 and displays me the respective column name in the gantt view. So for Project A the latest date is Milestone 4, for Project B it is Milestone 3 and for Project C it is Milestone 2.

So I need in addition to the latest date calculation the ourput of which Milestone it is.

I hope I could describe my problem well enlugh and I am hoping, that someone could help me please.

Many thanks and have a great day

Nika

[danextian · Accepted solution]

Hi @Nika777 ,

My approach would be to unpivot the milestone columnns and then either use measures or calculated columns to get the max date per project and the corresponding milestone.

Max Date Per Project = 
CALCULATE ( MAX ( MyData[Date] ), ALLEXCEPT ( MyData, MyData[Project] ) )

Max date per project (calc column) = 
CALCULATE ( MAX ( MyData[Date] ), ALLEXCEPT ( MyData, MyData[Project] ) )

Milestone with max date per project = 
CALCULATE (
    SELECTEDVALUE ( MyData[Milestone] ),
    FILTER (
        ALLEXCEPT ( MyData, MyData[Project] ),
        MyData[Date] = [Max Date Per Project]
    )
)

Milestone with max date per project (calc column) = 
CALCULATE (
    MAX ( MyData[Milestone] ),
    FILTER (
        ALL ( MyData ),
        MyData[Date] = EARLIER ( MyData[Max date per project (calc column)] )
            && MyData[Project] = EARLIER ( MyData[Project] )
    )
)

Please see attached pbix for your reference.

[Nika777]

Dear Danextian,

oh very cool, many thanks. As I need to keep the dates in one horizontal row (instead of columns) I could not apply this directly, but your approach helped me to transfer this to the horizontal in MS Excel first and then to transfer it to Power BI to make the visualizations. So many many thanks for this quick help 🙂