Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
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
Solved! Go to Solution.
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.
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.
Hi,
Yes, it can be done. Share data in a format that can be pasted in an MS Excel file.
Hi @luzsoulez
Check this link, might be helpful:
https://www.vahiddm.com/post/weekly-time-intelligence-dax
If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
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 |
I solved it with this post
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.
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
Days:
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..
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
111 | |
104 | |
103 | |
87 | |
61 |
User | Count |
---|---|
162 | |
132 | |
131 | |
95 | |
86 |