• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
    • Password Reset
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Showing an event lasting several weeks in a pivot table or graph (not gantt)|Dashboards & Charts|Excel Forum|My Online Training Hub

You are here: Home / Showing an event lasting several weeks in a pivot table or graph (not gantt)|Dashboards & Charts|Excel Forum|My Online Training Hub
Avatar
sp_LogInOut Log In sp_Registration Register
sp_Search Search
Advanced Search|Last Search Results
Search
Forum Scope




Match



Forum Options



Minimum search word length is 3 characters - maximum search word length is 84 characters
sp_Search Search
sp_RankInfo
Lost password?
sp_CrumbsHome HomeExcel ForumDashboards & ChartsShowing an event lasting several we…
sp_PrintTopic sp_TopicIcon
Showing an event lasting several weeks in a pivot table or graph (not gantt)
Avatar
Eleanor Poujol
Member
Members
Level 0
Forum Posts: 6
Member Since:
April 27, 2021
sp_UserOfflineSmall Offline
1
May 25, 2021 - 6:11 pm
sp_Permalink sp_Print

Hi everybody,
I've been learning to use Power Query and Power Pivot with these great tutorials, thanks. I'm working on a Dashboard which I've been asked to do. The Dashboard doesn't concern projects but the topic is similar so I'm calling them projects for simplicity.

I am stuck trying to do a bar chart (or pivot table) showing total impact over several weeks, of projets for which I have a start date and end date (and duration in weeks if needed). They don't want a gantt chart (which is what I originally suggested, using the Excel project tutorial). My projects are grouped by type in pivot table, and I need to be able to filter these as needed.
I can do my bar chart or pivot table, but it only shows the start date impact.

For example,

Project A starts Week 12, ends Week 16, has an impact of 2 (IMPmetier, medium for users)
Project B starts Week 14, ends week 18, has an impact of 3 (IMPmetier strong for users)

I want weeks 14, 15 and 16 to show total impact of 5 but can't get a measure to do this, I've tried all sorts of functions and am stuck !
Does anybody know what function I can use please (I've tried DATEDIFF and all sorts of others too) ?

My current measure is : IF(ISBLANK(SUM([IMPmetier])),0,SUM([IMPmetier])) that way I get all the weeks on bar chart even if no project.

Many thanks,

Eleanor

ps I Don't have the function SEQUENCE on my Excel.

Avatar
Mynda Treacy
Admin
Level 10
Forum Posts: 4449
Member Since:
July 16, 2010
sp_UserOfflineSmall Offline
2
May 26, 2021 - 1:30 pm
sp_Permalink sp_Print

Hi Eleanor,

Welcome to our forum! It's great to hear you're making use of the techniques you've learnt in my tutorials.

It's very difficult to follow your question without a sample Excel file. I can't tell what the source data is from the charts in the Word doc or how those charts were constructed etc.

If your file contains sensitive information then you can anonymise it, or create a mock-up with some dummy data. We don't need a lot of data, it just needs to illustrate your requirements and have the same structure as your actual data file i.e. if there's a Power Pivot model then the mock-up needs to have this too.

If the file is too big to upload to the forum, you can share a link to the file saved on OneDrive or Dropbox or similar.

Mynda

Avatar
Eleanor Poujol
Member
Members
Level 0
Forum Posts: 6
Member Since:
April 27, 2021
sp_UserOfflineSmall Offline
3
May 26, 2021 - 11:24 pm
sp_Permalink sp_Print

Thanks for your reply Mynda,

I'm joining my files which should be small enough in size.

Calendar file, DIMtype file, the ones that will move around are METIERS and HUB.

On the METIERS file, as you see there is a start and end date. I've created a measure on that table called TESTDUREE to be able to do the addition I'm trying to do, it's now telling me I have a format problem with the column SEMdebut. Yet I've made sure that is a number format, I even added VALUE to make sure. Still not working, and I don't know if my formula will work anyway !

On the HUB file I only have start dates as each event only lasts 1 day (but I created start / end / durations to be able to compile with METIERS file).
Some HUB dates I use in my formulas are empty, that is normal and the file needs to work in spite of that !

The chart needs to end up like on the TDB_CDR file, but I'm still doing this manually as I have the problem I contacted you about, and also a problem joining the information from the 2 files (which is why I created the DIMtype file, to try and join them properly).....

Any help would be really appreciated !

Thanks again,

Eleanor

Avatar
Mynda Treacy
Admin
Level 10
Forum Posts: 4449
Member Since:
July 16, 2010
sp_UserOfflineSmall Offline
4
May 27, 2021 - 4:21 pm
sp_Permalink sp_Print

Hi Eleanor,

Thanks for sharing your files. Just the file containing the Power Pivot model would have been sufficient as we don't need to refresh any connections to troubleshoot.

That said, I can't follow what the issue is because you explained it in English:

"For example,

Project A starts Week 12, ends Week 16, has an impact of 2 (IMPmetier, medium for users)
Project B starts Week 14, ends week 18, has an impact of 3 (IMPmetier strong for users)

I want weeks 14, 15 and 16 to show total impact of 5 but can't get a measure to do this, I've tried all sorts of functions and am stuck !"

But the file is in French and unfortunately, I don't speak French so I'm not able to translate it.

Also, the TDB_CDR table only contains some cells with formulas and I don't know how the other values were arrived at.

One thing I did fix is to change the table the SommeIMPACTdeploiements measure is attached to, from Metiers to Hub, the relationship warning then goes away.

Let's tackle one problem at a time. Please let me know which measure you're having trouble with and what it's supposed to calculate and what results you're expecting, so I can check my results.

Mynda

Avatar
Eleanor Poujol
Member
Members
Level 0
Forum Posts: 6
Member Since:
April 27, 2021
sp_UserOfflineSmall Offline
5
May 27, 2021 - 10:10 pm
sp_Permalink sp_Print

Hello Mynda,

My actual dream is to join tables HUB and METIERS to obtain a histogramme with both sets of data.

I'm looking to do, on the histogramme, a sort of GANTT chart, with weeks of the year as columns, and in the columns I need the IMPACT score of each event, (and total per Week of course). The TDB_CDR chart was done manually using information from pivot table HUB but for the METIERS table I had to do the scores manually as I can't get the right formula, and that is the one I contacted you for.

So, for now I have 2 charts, one for HUB and one for METIERS, I'm not having any particular difficulty with the HUB part, because the dates only last one day, so the IMPACT in the columns needs only to appear once, and so that is sorted (I'm using the measure sommeIMPACTdeploiements which I learned from your tutorials to show all weeks even those with no score.

I'm having difficulty with the IMPACT scores on the METIER file :
I can get the IMPACT score to come up on start day Week, in the same way as I do for the HUB file, using measure IMPACTmetier0siVIDE. BUT as this impact will last for the duration of that action, I want to repeat the IMPACT score throughout the action duration (we have a start date and end date which I have transformed into start Week and end Week, with also duration in weeks).
I've tried lots of functions, and I've created the measure called testduree. But no luck as yet.

in the METIERS table these are the columns needed I think :
SEMdeb (start Week), DUREESEM (duration in weeks), semFIN (end Week)

NO_SEM is my Week number from calendar table.

So to sum up and prioritize my wish list :

1. I need to show the METIERS impact scores throughout the duration of each action.
2. I need to represent all the actions, both HUB and METIERS, on one graph /gantt sheet (so that I can have a grand total of impact per Week 🙂 ). Ideally in the same pivot table but even by changing the relationships I've not managed to do so. Perhaps I should combine them in a power query file ?

Thanks once more for your help !

Avatar
Mynda Treacy
Admin
Level 10
Forum Posts: 4449
Member Since:
July 16, 2010
sp_UserOfflineSmall Offline
6
May 28, 2021 - 7:46 pm
sp_Permalink sp_Print

Hi Eleanor,

Thanks for providing further information. I'm still a bit in the dark as to what this data represents, but from what you've explained it sounds like you need to count a score on days where you don't actually have data. e.g. week 1,  for start week 10 duration is 17. But you don't actually have 17 records, just one record that tells you there should be 17 days worth of scores.

I think you need to create a row for each day there should be an impact score in your source data, rather than using the duration field, as this has no meaning from a time perspective, which is how you want to visualise the data. e.g. using the week 10 example, you need rows for week 10 through 27 with the scores you want to display in the Gantt chart. As opposed to the one row you currently have. Hope that makes sense.

Lesson 7.08 in the Power Query course explains how to expand data into rows.

Mynda

sp_Feed
Go to top
Forum Timezone: Australia/Brisbane
Most Users Ever Online: 245
Currently Online: Nada Perovic, Denise Lloyd
Guest(s) 9
Currently Browsing this Page:
1 Guest(s)
Top Posters:
SunnyKow: 1432
Anders Sehlstedt: 871
Purfleet: 412
Frans Visser: 346
David_Ng: 306
lea cohen: 219
A.Maurizio: 202
Jessica Stewart: 202
Aye Mu: 201
jaryszek: 183
Newest Members:
Raj Mattoo
Mark Luke
terimeri dooriyan
Jack Aston
AndyC
Denise Lloyd
michael serna
mashal sana
Tiffany Kang
Leah Gillmore
Forum Stats:
Groups: 3
Forums: 24
Topics: 6219
Posts: 27276

 

Member Stats:
Guest Posters: 49
Members: 31903
Moderators: 3
Admins: 4
Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea, FT
Moderators: MOTH Support, Velouria, Riny van Eekelen
© Simple:Press —sp_Information

Sidebar

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk - For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.