Forum Discussion

inescosta's avatar
inescosta
Icon for Advocate II rankAdvocate II
9 years ago
Solved

Date multiplications

 

I need to find a way to unfold the time period between [inicioProjeto] and [FimProjeto] in order to recreate a table with all the bussiness days between the two dates.

 

My final goal is to multiple the [tempomediodia] by each days in a civil year (between those dates).

Need Help
  • PavelR's avatar
    PavelR
    9 years ago

    What is the reason, that you can!t get it to work?

     

    By the way, what is the purpose to do this data transformation? Why you need this column?

     

    Pavel

10 Replies

  • inescosta's avatar
    inescosta
    Icon for Advocate II rankAdvocate II
    9 years ago

    Hello,

     

    This table has data refering to projects; each project has a set of hours allocated to them.

    I want to know the nr of hours allocated to each project by year.

    My problem is that some projects go on for over a year (the start date is the column START and the end date is the column END), I want to take the hours in column [quant] and divide them equally whithin the duration of the project. And after sum them by year.

    Thank you for your help

    • parry2k's avatar
      parry2k
      Icon for Super User rankSuper User
      9 years ago

      You can add another column and calculate hours between start and end date and here is DAX formula:

       

      ProjectHours = DateDiff(Table1[StartDate], Table1[EndDate], HOUR)

      Replace start date and end date with your fields. Let me know if need further help.

      • inescosta's avatar
        inescosta
        Icon for Advocate II rankAdvocate II
        9 years ago

        Thank you for the help but that is not exactly what I need.

        I know the hours dedicated to the project...they are in column [quant]. For example for the project id 57156 I will have a total of 5 hours for that project, but the project starts in 13_04_2016 and ends in 01_01_2020. That means I will have to divide those 5 hours for that period. I need to find a way to divide my 5 hours for the project duration and them sum them by years.

        Thank you

  • PavelR's avatar
    PavelR
    Icon for Solution Specialist rankSolution Specialist
    9 years ago

    inescosta so you want to get the date table, which will have dates between the lowest date InicioProjeto and highest date FimProjeto? If so, then use CALENDAR(MIN(Table[InicioProjeto]);MAX(Table[FimProjeto]))

     

    Regards.

    Pavel