Forum Discussion

Kristofferaabo's avatar
Kristofferaabo
Icon for Helper IV rankHelper IV
8 years ago
Solved

Sateset to return first and last date

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"

Need Help
Show and Tell
  • v-jiascu-msft's avatar
    v-jiascu-msft
    8 years ago

    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] )
)

    Sateset_to_return_first_and_last_date

     

    Best Regards,

    Dale

5 Replies

  • v-jiascu-msft's avatar
    v-jiascu-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    8 years ago

    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] )
)

    Sateset_to_return_first_and_last_date

     

    Best Regards,

    Dale

    • Kristofferaabo's avatar
      Kristofferaabo
      Icon for Helper IV rankHelper IV
      8 years ago

      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's avatar
        Kristofferaabo
        Icon for Helper IV rankHelper IV
        8 years ago

        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