Project-based relative dates

[jukeysmoot]

Hi, Power Bi beginner here.

My question is for a broader model structure. I'm attempting to establish relative project dates, and ideally, week numbers for different projects my company does.

We are in construction, and while we do similar projects i'd like to compare costs for, even similar projects over the years have differing start and end dates.

I have a transaction table set up, which essentially lists every transaction we have, and a date on it. The key relationship between that table and our "job info" table is "job number". I have a calendar reference table, which includes week numbers, but references week of the year, month of the year, etc.

Ideally, I'd like tie in "transaction date" on the transaction table to "project day" (i.e., transaction date - job start date), and "project week" using a similar function as the WEEKNUM function. This would allow me to compare the costs of project A and project B, that may have separate start dates, through equivalent days and weeks of the projects.

I'm unsure if I should seek to add a calculated column or measure to the transaction table, which just assigns a project day and week to the transaction day, or if I should seek to establish a separate reference table. The only issue with a separate reference table is that we have many jobs going at once, so a particular calendar date could be "Day x" for "job A" and "Day y" for Job B.

Apologies if that explanation is at all unclear, and I appreciate any ideas that could help me out!

[kentyler]

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 ken@8thfold.com

Thanks

[jukeysmoot]

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!

[jukeysmoot]

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!

[kentyler]

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"

[kentyler]

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

[jukeysmoot]

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!

[kentyler]

i'm a big fan of doing more than just "reporting" on data. if you do decide to try using some SPC methods to get more insight i'd love to talk more about it.