Forum Discussion

luzsoulez's avatar
luzsoulez
Icon for Helper I rankHelper I
4 years ago
Solved

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...
Need Help
  • luzsoulez's avatar
    luzsoulez
    Icon for Helper I rankHelper I
    4 years ago

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

  • luzsoulez's avatar
    luzsoulez
    Icon for Helper I rankHelper I
    4 years ago

    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
    • Ashish_Mathur's avatar
      Ashish_Mathur
      Icon for Super User rankSuper User
      4 years ago

      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's avatar
        luzsoulez
        Icon for Helper I rankHelper I
        4 years ago

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