Sateset to return first and last date

[Kristofferaabo]

Hi all,

I have a large data set with multiple 'project timelines'. I want to to get the first date and the last date per country for each of these projects..

What I currently have doesn't filter in the project. The way I would solve that in excel would be to make a pivot as the attached picture below:

My wish would be:

1. A smart DAX formula that can return the first date (start date) and the last date (project end) per country AND per project

2. or a way I can set up a that same type of pivot in powerBI, that way I would connect/link the pivot table to my 'master sheet' throgh the project code.

I hope this makes sense

Thanks

Kristoffer

"MASTER SHEET"

"PIVOT SHEET"

[v-jiascu-msft · Accepted solution]

Hi @Kristofferaabo,

I created a featured sample. There could be two solutions. 

1. Create a table visual directly.

2. Create a calculated table.

Table =
SUMMARIZE (
    'Table1',
    Table1[Country],
    Table1[Porject],
    "Start Date", MIN ( 'Table1'[Start Project] ),
    "End Date", MAX ( 'Table1'[End Project] )
)

Best Regards,

Dale

[Kristofferaabo]

Hi,

Thanks the SUMMARIZE table absolutely does the job! - Do you know how I can make these distinct?

Normally I would 'remove duplicates' in the query editor, but I cant find the table in there?

Any ideas?

Krsitoffer

[Kristofferaabo]

Hi @v-jiascu-msft,

Is there anyway I can make the SUMMARIZE function only to take distinct values in, or have that column remove duplicates afterwards? I cant see the table in the query editor..

Thanks

Kristoffer

[v-jiascu-msft]

Hi @Kristofferaabo,

Generally, the result of Summarize is unique. Can you share a more featured sample? If the duplicates are exactly the same, we don't need to care them due to the function MIN, MAX will do. If they are just partly same, please point out how to keep the desired values.

Best Regards,

Dale

[Kristofferaabo]

HI @v-jiascu-msft,

Thanks for your good reply, this makes sense...

Looking at this more detailed. I think the problem is NULL values. Is there a way I can remove those from the master table, then I'm sure I can link the table.

Thanks