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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.