Weekly Gross Profit by project

[luzsoulez]

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

[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.

[VahidDM]

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!! 

LinkedIn | Twitter | Blog | YouTube 

[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

[luzsoulez · Accepted solution]

I solved it with this post

https://natechamberlain.com/2018/08/08/how-to-add-rows-for-dates-between-start-and-end-dates-in-powe....

[Ashish_Mathur]

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.

[luzsoulez]

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 setting

var _fromYear=2009 // set the start year of the date dimension. dates start from 1st of January of this year

var _toYear=2022 // set the end year of the date dimension. dates end at 31st of December of this year

var _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()

return

ADDCOLUMNS(

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)

)

[luzsoulez]

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..