Assistance with Returning a date based on a value repeated for a category.

[JimmyD]

Hello,

My Company has recently started using Power BI and have been used to SAP BO functions on manipulating data. DAX is a whole new world for me and am Stumped on this one...

I have a list of Projects and their programme, the first task is whole project - I would like to grab the "Finish Date" where Id=1 foreach Project Number. I tried to Lookup but it only likes returning 1 value and wasn't sure how to use a filter for each Project Number here. Is there any function that could help me do this- See picture of sample data;

As we just started using this the list is small, but it will always be adding new ones and could be quite a large list of projects.

[Anonymous]

Hi @JimmyD ,

What is the expected out put of your sample data?

Best Regards,

Jay

[amitchandak]

@JimmyD , Can you share sample data and sample output in table format?

[JimmyD]

I believe you mean the below?

The aim of this is to get the Daysbetween D & C and Daysbetween D & F and then the difference between the products of them so I can track the Days Ahead or Behind Schedule each project is tracking on graphs and then have a company average of days ahead or behind.

If there is a better way of calculating this I am happy to help but this was the best way I could think of.

[Anonymous]

Hi @JimmyD ,

I'm sorry that i didn't quite understand what you want.

But If you want calculate the days between two dates, you may use DATEDIFF() function.

And if you want average of the days group by company you may use AVERAGE() and ALLEXCEPT() function.

Best Regards,

Jay

[JimmyD]

Thanks for the reply.

In the end as we were only gathing that data i just transformed the Data table to only show ID 1 as this was all we were using.