Forum Discussion
Project-based relative dates
It sounds like the granularity of your basic information is expense/by project/by day understanding that all days might not be filled in for all projects
and you say you want to compare "similar projects", meaning projects that take the same number of days
so if you had a measure that returned the total number of days for a given project
you could do a report based on your project table that listed the number of days
you might want to expand your definition of "similar" to be "within 5 days of each other"
so then you could use the https://www.daxpatterns.com/dynamic-segmentation/ dynamic segmentation pattern to group your projects into buckets: 1-10 days, 1-15 days, etc
you could then add a measure that for a given project calculated which bucket it was in
if you put a slicer on a report that listed your buckets, when the user clicked on a bucket, they could see a list of the projects that fell into that bucket
you might want to add an estimated end date to projects that are still running, so you could assign them to a bucket as well. that would involve calculating the percent of their bucket they had consumed.
very interesting project. if you'd like to go over the options, i'd love to do a screen share. send me an email at [email protected]
Thanks
thanks for the quick response! I should have elaborated on similar projects: I actually meant scope of work type. For example, we do a lot of recurring work for a big client, whose different projects I have sorted into "customer" and "job type" for easy reference beyond job number, but even for those very similar projects scope-of-work-wise, we have had one recently that was 8 months, and a current one that is 6. A lot of the similarities between the two extend to that our labor is pretty consistent week to week, which forms the bulk of our variable costs.
So far, I've ended up doing something similar to what you suggested, within those "job types". I ended up establishing two new columns in the transaction file - "job day" (transaction date-start date), and "job week" (which i was able to bring up by using WEEKNUM on the "job day" column and adjusting it to suit whatever year that was calculated on (1900?), as a job day of "1" was originally returning "job week" of "53". Not the most elegant solution, but I think it will work for most of my comparative needs!
very interesting
i can see how you could end up comparing "week" numbers to jobs in a similar "job type"
this is a case where using something like XMR charts might give you the ability to rapidly see which jobs were showing "normal" differences and which were giving "signals" that something abnormal was going on. there are some good custom visuals that make building them really simple. see https://appsource.microsoft.com/en-us/product/power-bi-visuals/WA104382092?tab=Overview
wow, that is great! Right up our alley. I just came on board the company, but previously, we were in the habit of simply "reporting" costs, often at job completion, and the goal is start controlling them more in real-time. Tools like XMR could potentially be very helpful. Thanks for the tip!
another thing you might consider once you've acquired some data
if you also categorize things like customer type/ type of work / number of employees etc
you might be able to do regression analysis to find out what characterized projects that are "unstable", or that have cost overruns, or whatever
then when a project starts you could predict a little whether it would be "normal" or "dangerous"
and thanks for the tip on the dynamic segmentation! That will really help to be able to slice those "job types" into more user-friendly custom categories. I appreciate it!
