Cummulative hours from start to end date

[Anonymous]

Hi,

I´m trying to show cummulative hourse on a visualization, the thing is that i have two dates. Start and End Date.
I have this table:

Course	Total Hours	Stard Date	End Date
1	100	01/07/2019	08/09/2019
2	50	05/03/2019	08/06/2019
3	35	21/07/2019	21/09/2019
4	15	15/09/2019	08/12/2019

I need to show accumulative hours from thouse dates. The thing is that if i show this on a visualization, i can only put on X axis only one date, Start or End Date. How can i manage this?
If i make a "Calendar table" i can only relation it with or either Start Date or End date

Thanks

[Icey]

Hi @Anonymous ,

Is this problem sloved?

If it is sloved, could you kindly accept it as a solution to close this case?  

If not, please let me know.  

Best Regards

Icey

[Anonymous]

Hi @Icey,

Sorry is not solved. What I need to do is to make an average of amount of hours.
For example if you finish a course which start on April and finish on July; and the total course hours is 100, you would probably achieve 25 hours per month.
Do i explain myself? And this should be calculated base on start and end date that i mentioned
Thanks

[Icey]

Hi @Anonymous ,

You can use DATEDIFF function. These are my measures:

Days = DATEDIFF ( MAX ( 'Table'[Start Date] ), MAX ( 'Table'[End Date] ), DAY )

Hours per Day = DIVIDE ( MAX ( 'Table'[Total Hours] ), [Days] )

Months = DATEDIFF ( MAX ( 'Table'[Start Date] ), MAX ( 'Table'[End Date] ), MONTH )

Hours per Month = DIVIDE ( MAX ( 'Table'[Total Hours] ), [Months] )

This is my PBIX file.

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi @Icey 

That solution is perfect. The thing is that i have some courses that start and end in the same month. So when i execute those mesures i have some "Total hourse per month" in blank. And when i try to put an "Alternative result" when is "0" i can only put an constant numeric value (according to Power BI). And i need that in case of beeing "0" months it should show "Total Hours" column

Hope you understand

Regards

[Icey]

Hi @Anonymous ,

For Month = 0, try this:

Hours per Month =
IF (
    [Months] = 0,
    MAX ( 'Table'[Total Hours] ),
    DIVIDE ( MAX ( 'Table'[Total Hours] ), [Months] )
)

Best Regards,
Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi ICEY

This is almost ready. 
The thing is that when i show this on a visualization it doesent work well.
This is because, when i put in a X Axis the month, (taken from End Date) I lose information.
See below:

TotalHoras	StartDate	EndDate	Months	Hours per Month
48	03/05/2019	27/08/2019	3	16
48	03/05/2019	27/08/2019	3	16
48	03/05/2019	27/08/2019	3	16

But the 16 hours are only shown on month Agost or month May (depending on what date i use - Start or End)

I need that the 16 hs apear on June, July, And Agost.

Thanks!

[Ashish_Mathur]

Hi,

In a simple Table, please show the exact expected result.

[Icey]

Hi @Anonymous ,

You can create a column like this:

Start - End = COMBINEVALUES ( " - ", 'Table'[Start Date], 'Table'[End Date] )

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.