Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
luzsoulez
Frequent Visitor

Weekly 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

luzsoulez_4-1656622810098.png

Formula 2

luzsoulez_3-1656622791163.png

The final formula for visualization

luzsoulez_2-1656622762458.png

luzsoulez_1-1656622679105.png

#grossprofit #weeklycalc

Any ideas? Thanks!

Luz

1 ACCEPTED SOLUTION
9 REPLIES 9
Ashish_Mathur
Super User
Super User

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. 


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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

Badges.jpg

LinkedIn | Twitter | Blog | YouTube 

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.

idStatusStart DateEnd DateBurdenLoaded PayrateGross Profit HourGross Profit weekGross Profit Day
452696Approved8/30/20218/27/20221.17$67.30$25.36$1,014.20$202.84
448983Approved6/28/202112/31/20221.17$68.30$12.53$501.08$100.22
458540Approved12/27/202112/26/20221.05$86.10$25.70$1,028.00$205.60
457511Approved11/8/202112/31/20221.26$75.60$19.22$768.80$153.76
457421Approved11/8/202112/31/20221.26$73.70$17.30$692.00$138.40
456710Approved11/22/202112/31/20221.17$62.30$20.02$800.70$160.14
454331Approved9/27/202110/31/20221.17$76.10$22.26$890.40$178.08
450699Approved8/16/202110/30/20221.17$72.00$18.05$721.80$144.36
448293Approved6/14/202112/13/20221.17$83.10$24.69$987.60$197.52
445811Approved4/26/202112/31/20221.17$47.90$8.29$331.48$66.30
445060Approved5/17/202112/30/20221.26$75.60$15.63$625.20$125.04
444756Approved4/7/202112/31/20221.05$63.00$24.00$960.00$192.00
442322Approved3/4/20218/31/20221.05$63.00$21.00$840.00$168.00
434904Approved11/30/202010/30/20221.17$81.90$10.10$404.00$80.80
431472Approved9/21/202010/30/20221.17$67.90$17.07$682.80$136.56
425936Approved4/27/202010/26/20221.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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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:

luzsoulez_1-1657628382422.png

Week: I need the week to show the addition of the days, not the last day

luzsoulez_2-1657628517404.png

 

 

 

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

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

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.