I have the following problem, we work with projects, therefore they have a start and an end date. I want to see the profit by week of each project.
person XXX starts 10/02/2020 and finishes the project on 06/13/2022 - GP per day 30USD
person XXX2 starts 08/02/2022 and finished the project on 05/05/2022 - GP per day 50USD
I want to be able to see the GP by week making sure the project GP comes in/out correctly and accounts for only Monday-Friday, some projects can start/end mid-week.
This is what I have used for the following daily view, but it does not work for weekly totals
The final formula for visualization
Any ideas? Thanks!
Solved! Go to Solution.
Ideally there should be a 1 row per date and there is a way to do this in the Query Editor. However, this will increase the number of rows in the source data table. If you are OK with using this approach, then share the download link of your PBI file. Also, ensure there is a Calendar table in that file with a column of week number.
Hi, I understand what you mean, one line per transaction, I am not worried about the number of lines. can I generate the rows in the query if I have a start date and an end date to create all the rows in between? that would be the only way as the database does not have one line per day per person.
Yes, it can be done. Share data in a format that can be pasted in an MS Excel file.
Hi! sorry for the delay I was OOO last week. Thanks for any help you can provide
Please see a sample of the data below (I can't attach it). Ideally, the calculation is the Gross profit per day and aggregates to weeks to account for projects that start/end mid-week.
|id||Status||Start Date||End Date||Burden||Loaded Payrate||Gross Profit Hour||Gross Profit week||Gross Profit Day|
I cannot understand which there are the input columns and which are the output columns? You already have Gross profit per day - what else do you want? As requested earlier, share a Calendar table in the PBI file with a column of week number. Please also show the expected result clearly.
Hi, thanks for responding. The expected result is that between the start and the end date I can show every week how much gross profit it would be, as we track gross profit on a weekly basis. The problem is that the weekly view repeats whatever it is at the end of the week, which does not add up to the daily calculation
Week: I need the week to show the addition of the days, not the last day
The calendar is as follows
Thanks for the article is really good but still does not help me to resolve my current issue because I don't have a transaction per day. I have a transaction that starts one day and ends in the future and I need to show how it rolls through that entire period of time. let's forget about the week concept, if the project is active through one month I need to see the daily GP every day, although I don't have 1 line per day, I only have a starting point and an endpoint. after accomplishing that I can aggregate it into weeks, months, etc..
Click here to learn more about the September 2022 updates!
Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.
Mark your calendars and join us on Thursday, October 6 at 11a PDT for a great session with Ted Pattison!