Forum Discussion

smsayedee's avatar
smsayedee
Copper Contributor
May 30, 2023
Solved

Help needed - filter, power queries and pivot

Hi All,   I've a table with below details: Column A: Activity Name Column B: Start Date Column C: End Date   Now an activity can be completed in a day, so the start and end date are the same o...
excel
Formulas and Functions
smsayedee's avatar
smsayedee
Copper Contributor
Jun 03, 2023

Lorenzo Thanks for your help. Its really working. However, I've encountered another problem while solving one. For example, if I create a pivot table where I want to see the count of activities based on progress status it counts one activity 5 times where that activity spans over 5 months. In the below example, although there are only 4 distinct activities, Activity1 was counted 6 times since the start date was 23/03/2023 and end date was 25/04/2023 and 3 Person was involved, hence 3x2=6.

 

Could you please advise how can I resolve this so that if I create such pivot if would only reflect the correct number which is 4 with 1 completed, 2 in progress and 1 is planned. I've attached the spreadsheet for your convenience. Thanks once again.

  • Lorenzo's avatar
    Lorenzo
    Silver Contributor
    Jun 04, 2023

    smsayedee 

    Linking posts is appreciated, marking solutions helps people who Search - Thanks

    See attached file that does what you expect in term of Count by Activity Status