Forum Discussion
Weekly Gross Profit by project
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 |
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
Date_Master =//************** Script developed by RADACAD - edition: July 2021//************** set the variables below for your custom date table settingvar _fromYear=2009 // set the start year of the date dimension. dates start from 1st of January of this yearvar _toYear=2022 // set the end year of the date dimension. dates end at 31st of December of this yearvar _startOfFiscalYear=7 // set the month number that is start of the financial year. example; if fiscal year start is July, value is 7//**************var _today=TODAY()returnADDCOLUMNS(CALENDAR(DATE(_fromYear,1,1),DATE(_toYear,12,31)),"Year",YEAR([Date]),"Start of Year",DATE( YEAR([Date]),1,1),"End of Year",DATE( YEAR([Date]),12,31),"Month",MONTH([Date]),"Start of Month",DATE( YEAR([Date]), MONTH([Date]), 1),"End of Month",EOMONTH([Date],0),"Days in Month",DATEDIFF(DATE( YEAR([Date]), MONTH([Date]), 1),EOMONTH([Date],0),DAY)+1,"Year Month Number",INT(FORMAT([Date],"YYYYMM")),"Year Month Name",FORMAT([Date],"YYYY-MMM"),"Day",DAY([Date]),"Day Name",FORMAT([Date],"DDDD"),"Day Name Short",FORMAT([Date],"DDD"),"Day of Week",(WEEKDAY([Date],2)),"Day of Year",DATEDIFF(DATE( YEAR([Date]), 1, 1),[Date],DAY)+1,"Month Name",FORMAT([Date],"MMMM"),"Month Name Short",FORMAT([Date],"MMM"),"Quarter",QUARTER([Date]),"Quarter Name","Q"&FORMAT([Date],"Q"),"Year Quarter Number",INT(FORMAT([Date],"YYYYQ")),"Year Quarter Name",FORMAT([Date],"YYYY")&" Q"&FORMAT([Date],"Q"),"Start of Quarter",DATE( YEAR([Date]), (QUARTER([Date])*3)-2, 1),"End of Quarter",EOMONTH(DATE( YEAR([Date]), QUARTER([Date])*3, 1),0),"Week of Year",WEEKNUM([Date],2),"Start of Week", [Date]-WEEKDAY([Date],3),"End of Week",[Date]+7-WEEKDAY([Date],2),"Fiscal Year",if(_startOfFiscalYear=1,YEAR([Date]),YEAR([Date])+ QUOTIENT(MONTH([Date])+ (13-_startOfFiscalYear),13)),"Fiscal Quarter",QUARTER( DATE( YEAR([Date]),MOD( MONTH([Date])+ (13-_startOfFiscalYear) -1 ,12) +1,1) ),"Fiscal Month",MOD( MONTH([Date])+ (13-_startOfFiscalYear) -1 ,12) +1,"Day Offset",DATEDIFF(_today,[Date],DAY),"Month Offset",DATEDIFF(_today,[Date],MONTH),"Quarter Offset",DATEDIFF(_today,[Date],QUARTER),"Year Offset",DATEDIFF(_today,[Date],YEAR))