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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
inescosta
Advocate II
Advocate II

Date multiplications

 

imagem.png

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

1 ACCEPTED SOLUTION
PavelR
Solution Specialist
Solution Specialist

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

View solution in original post

10 REPLIES 10
PavelR
Solution Specialist
Solution Specialist

@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

http://community.powerbi.com/t5/Desktop/Getting-all-dates-between-2-dates/m-p/61211/highlight/false

 

this is what I am trying to get...but I just can't seem to do it.

 

Using calendar() it will give me a different table, but then I need to associate values from the tables that I am using to that one.

 

What is explained in that post is perfect but I can't get it to work.

 

thank you

PavelR
Solution Specialist
Solution Specialist

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

The column [quant] has the number of hours I can use in a particular project. the columns with the dates are the beginning and the end of the project. What I want in the end is a measure that gives me the nr of hours I can use per year in that particular project.

 

So this is what I am thinking:

 

1-I calculated how many days are between the two dates: column [nrdiasProjeto].

2-I divided the [quant] per [nrdiasProjeto] that gives me the average nr of hours I can use per day: column:[tempomediodia]

3- Know I would need a table with all the days between those dates and each day would have the average nr of hours allocated to them,

 

this way I coul them sum them by civil year.

 

 

 

 

 

inescosta
Advocate II
Advocate II

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.

imagem.png

Thank you for your help

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.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

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

What is the logic to divide those 5 hours across years? 

 

 



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

I need to compare the hours allocated to a project in a budget and the real hours used. In each year I can only use [quant]/duration of the project.

imagem.png

Maybe if I 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).

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.