Recently I came across a very interesting problem, I thought I’ll share it with you all and learn different ways of solving this.

 

Here is a simple 10 row data with Project Name, Travel Start Date and Travel End Date.

Problem : Your task is to find the number of unique travel days for each project. For instance, the dates for Project 3 (in row 2 and 3) are over lapping. Overlapping dates for a particular project need to be counted once.

 

Here is how the output should look like

 

DOWNLOAD THE PROBLEM EXCEL FILE

 

Take a shot at doing this. Couple of instructions

  1. Don’t start grappling with this manually. Come up with a technique that is automated.
  2. You may use Excel, Power Query or even Power Pivot (DAX) for it.
  3. Personally I am interested to see how can you solve this using Excel or DAX. Solving this using Power Query is a cakewalk.
  4. I’ll post my solution in a day or two.

 

My Solution: Posted on 22 Nov 2018

I have solved this using

  • Excel’s TEXTJOIN function + a bunch of patchy manual work
  • Power Query’s Number.From function
  • And Using DAX – The main highlight is using the GENERATE Function

 

DOWNLOAD THE SOLVED EXCEL FILE

 

Please post your solutions in the comments, if need be please leave a link to download your file. Cheers

 

 



Topics that I write about...






Download Smart Ebooks on
Excel and Power BI