Forum Discussion
How Do I Make a Chart That Counts Activities Start/Finish Within Specific Month
I literally found out about Power Bi yesterday so I'm learning and thank you to whoever helps! I've tried looking through people's posts with similar setups but i'm just too new to take the codes and adjust to fit to my exact purpose.
I have a table full of activities with start and finish dates (2 different columns) with the file # tied to each row.
Table: Updates
Columns:Update#, Activity ID, Activity Status, Start Date, Finish Date, Budget, Actual Billed
The goal is to have a chart that can quickly show me the Update#, number of Planned Activities (source table: "Updates">2 Columns (Start/Finish & Gets the correct UP#)), number of Actual Activities (source table: "Updates">2 Columns (Start/Finish & Gets the correct UP#), Calculate % Hit of Planned/Actuals
Example Product:
Update#1, Nov-19, Planned 50, Actual 46, 92%
Update#2, Dec-19, Planned 60, Actual 45, 75%
File #3... etc..
I understand I have to make a new measure but I have no idea what codes work here to get me to count the activities that fit within Nov-19 for example.
The code has to check UP#0 to get the planned count for UP#1 (the file numbers mean progress of a month), so to get the planned # count of Update #2 will have to come from Update #1 - Start / Finish planned dates.
Would it also be possible to click the planned activities number and open the list of the activities planned and also be able to click the actual number and link to a chart of the activities that were actually closed start/finish?
4 Replies
- amitchandak
Super User
Refer, if this HR blog can help you. This deal with Start and End Date
Thanks amitchandak
I downloaded your file and all of it makes sense. I'm understanding what your process is & the calculations, I'm just limited by my knowledge of codes for this. I can do this for excel just not on here. What I'd like to do is:
I have a table called Updates.
I have all the activities per update, I'd like to have a planned activities count.
I'd like to know how many planned activities were in UP01, to do that I have to cross reference -1 update back, so references UP00.
(It's a bit similar to how you check your Last Period Employee Calc where it goes back a month)
How do I get PBI to understand what month to filter for each UP#? Like UP00 is December-19, UP01 is January-20.
And then only filter for activities planned to start within that month. While being able to adapt as the log gets bigger and more updates are added.
- edhans
Community Champion
How to get good help fast. Help us help you.
How to Get Your Question Answered Quickly
How to provide sample data in the Power BI Forum - If you've just started learning... then please do yourself a favor and find some good resources to learn; maybe a short course or an article/manual/guide - there are plenty on the net but go to respectable sites (check out the one by Matt Allington or www.sqlbi.com); there are many that give you advice of low quality because they don't know how to do the DAXy things properly.
DAX is much more than just coding measures. You have to know how to build correct models first.
You can start here:
https://www.youtube.com/watch?v=78d6mwR8GtA
https://www.youtube.com/watch?v=_quTwyvDfG0
Best
D
