Forum Discussion
Helper IWeekly Gross Profit by project
Hi,
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
Formula 1
Formula 2
The final formula for visualization
#grossprofit #weeklycalc
Any ideas? Thanks!
Luz
9 Replies
luzsoulezThanks 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..
- luzsoulez
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 452696 Approved 8/30/2021 8/27/2022 1.17 $67.30 $25.36 $1,014.20 $202.84 448983 Approved 6/28/2021 12/31/2022 1.17 $68.30 $12.53 $501.08 $100.22 458540 Approved 12/27/2021 12/26/2022 1.05 $86.10 $25.70 $1,028.00 $205.60 457511 Approved 11/8/2021 12/31/2022 1.26 $75.60 $19.22 $768.80 $153.76 457421 Approved 11/8/2021 12/31/2022 1.26 $73.70 $17.30 $692.00 $138.40 456710 Approved 11/22/2021 12/31/2022 1.17 $62.30 $20.02 $800.70 $160.14 454331 Approved 9/27/2021 10/31/2022 1.17 $76.10 $22.26 $890.40 $178.08 450699 Approved 8/16/2021 10/30/2022 1.17 $72.00 $18.05 $721.80 $144.36 448293 Approved 6/14/2021 12/13/2022 1.17 $83.10 $24.69 $987.60 $197.52 445811 Approved 4/26/2021 12/31/2022 1.17 $47.90 $8.29 $331.48 $66.30 445060 Approved 5/17/2021 12/30/2022 1.26 $75.60 $15.63 $625.20 $125.04 444756 Approved 4/7/2021 12/31/2022 1.05 $63.00 $24.00 $960.00 $192.00 442322 Approved 3/4/2021 8/31/2022 1.05 $63.00 $21.00 $840.00 $168.00 434904 Approved 11/30/2020 10/30/2022 1.17 $81.90 $10.10 $404.00 $80.80 431472 Approved 9/21/2020 10/30/2022 1.17 $67.90 $17.07 $682.80 $136.56 425936 Approved 4/27/2020 10/26/2022 1.17 $79.60 $7.55 $302.00 $60.40 - Ashish_Mathur
Super User
Hi,
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.
- Ashish_Mathur
Hi,
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.
- luzsoulez
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.
- Ashish_Mathur
Hi,
Yes, it can be done. Share data in a format that can be pasted in an MS Excel file.
