Measure to return the max date of a project?

[Kristofferaabo]

Hi all,

I have a set of data which has projects listed with multiple dates, I would like to make a measure that returns the latest date by project.

Im not interested to do it through a table, but would like it as a measure or a column so I can use it for forther calculation.

Thanks

Krsitoffer

[MFelix]

Hi @Kristofferaabo,

First of all when you use a measure this is based on context so depending on the context of your information you will get different results off course you can use several ways to them get the final result like filters.

You only need to make the following measure:

Last Date = MAX (Table[Date])

This will return the maximum date of the project if you add the project number on your visuals / graphs / ...

As Isaid this is context based so you have to use it with the correct information to get it on project level.

Can you please explain what type of adittional calculations you want to do?

Regards,

MFelix 

[Kristofferaabo]

Hi @MFelix!

Thanks for your answer I really appreciate it, the idea might work but I have not thought a bouth as I was afraid it wouldn't be able to differentiate between the various projects.

An example of What I would like is the this:

My with is to have a measure/columns that would return 2016-05-18 s a 'stop date' for each of these three project lines..

That way I would get the longest project duration by project

[MFelix · Accepted solution]

Hi @Kristofferaabo,

For this I would use something like this:

MAXIMUM_END_DATE =
IF (
    HASONEFILTER ( Table1[Project] );
    CALCULATE ( MAX ( Table1[End Project] ); ALLEXCEPT ( Table1; Table1[Project] ) );
    BLANK ()
)

This will return the maximum date for the End project column and you can use it to make different calculations:

DURATION = DATEDIFF( MIN(Table1[Start Project]);[MAXIMUM_END_DATE];DAY)

Result:

The total in the example is incorrect, don't look at it. It can be improved is just an example.

Regards,

MFelix