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
Lorenzo's avatar
Lorenzo
Silver Contributor
Jun 01, 2023

smsayedee 

excel on windows. But the spreadsheet actually saved in sharepoint for collaboration. So other user can use it from other platform. Does it matter?

I was thinking of using the Data Model (Power Pivot). Will avoid then...

smsayedee's avatar
smsayedee
Copper Contributor
Jun 01, 2023
I’ve been using data model, no issues so far!
  • 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

  • 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.