Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Kristofferaabo
Helper IV
Helper IV

Measure to return the max date of a project?

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

 

projectdates_ex.jpg

1 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:

 

allex.png

 

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

 

Regards,

MFelix

 

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

3 REPLIES 3
MFelix
Super User
Super User

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 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



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


date-exa2.jpg


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:

 

allex.png

 

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

 

Regards,

MFelix

 

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.