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
Julver
Frequent Visitor

Timeline dowtime group by hour

Hi, I'm trying to create a chart like below:

 

Julver_0-1613494988862.png

 

 

The data is similar to:

EQUIPSTART TIMEFINISH TIMECATEGOY COLOR
HT0011/01/2020 09:00:021/01/2020 09:45:00UNSCHEDULED
HT0031/01/2020 09:50:011/01/2020 14:58:12BACKLOG
HT0041/01/2020 12:50:231/01/2020 14:50:00UNSCHEDULED
HT0021/01/2020 17:18:012/01/2020 04:00:09SCHEDULED

 

how I can solve this?,  Thank you in advance for any advice!

1 ACCEPTED SOLUTION
MFelix
Super User
Super User

Hi @Julver ,

 

First you need to create a calendar table with date time something simlar to this:

 

DateTime = 
ADDCOLUMNS (
    CROSSJOIN (
        CALENDAR ( DATE ( 2020, 1, 1 ), DATE ( 2020, 12, 31 ) ),
        UNION (
            ROW ( "Time", TIME ( 1, 0, 0 ) ),
            ROW ( "Time", TIME ( 2, 0, 0 ) ),
            ROW ( "Time", TIME ( 3, 0, 0 ) ),
            ROW ( "Time", TIME ( 4, 0, 0 ) ),
            ROW ( "Time", TIME ( 5, 0, 0 ) ),
            ROW ( "Time", TIME ( 6, 0, 0 ) ),
            ROW ( "Time", TIME ( 7, 0, 0 ) ),
            ROW ( "Time", TIME ( 9, 0, 0 ) ),
            ROW ( "Time", TIME ( 10, 0, 0 ) ),
            ROW ( "Time", TIME ( 11, 0, 0 ) ),
            ROW ( "Time", TIME ( 12, 0, 0 ) ),
            ROW ( "Time", TIME ( 13, 0, 0 ) ),
            ROW ( "Time", TIME ( 14, 0, 0 ) ),
            ROW ( "Time", TIME ( 15, 0, 0 ) ),
            ROW ( "Time", TIME ( 16, 0, 0 ) ),
            ROW ( "Time", TIME ( 17, 0, 0 ) ),
            ROW ( "Time", TIME ( 18, 0, 0 ) ),
            ROW ( "Time", TIME ( 19, 0, 0 ) ),
            ROW ( "Time", TIME ( 20, 0, 0 ) ),
            ROW ( "Time", TIME ( 21, 0, 0 ) ),
            ROW ( "Time", TIME ( 22, 0, 0 ) ),
            ROW ( "Time", TIME ( 23, 0, 0 ) ),
            ROW ( "Time", TIME ( 24, 0, 0 ) )
        )
    ),
    "DateTime", [Date] + [Time],
    "Hour", HOUR ( [Time] )
)

 

Now create the following measure:

 

Count based on hours =
CALCULATE (
    COUNT ( 'Table'[EQUIP] ),
    FILTER (
        'Table',
        'Table'[FINISH TIME] >= MAX ( DateTime[DateTime] )
            && 'Table'[START TIME] <= MAX ( DateTime[DateTime] )
    )
)

This gives the result below using the Date time has an axis and the measure in values:

 

MFelix_0-1613569504268.png

Looking at your picture seems to me that you are not considering the seconds in each of the start and finish so I created two new columns:

FinishTimeNoSeconds = MROUND ( 'Table'[FINISH TIME], TIME ( 1, 0, 0 ) ) + TIME ( 0, 0, 0 )

StartTimeNoSeconds = MROUND ( 'Table'[START TIME], TIME ( 1, 0, 0 ) ) + TIME ( 0, 0, 0 )

This removes the seconds from the start and finish now you just need to redo your measure to:

 

Count based on hours no seconds = 
CALCULATE (
    COUNT ( 'Table'[EQUIP] ),
    FILTER (
        'Table',
        'Table'[FinishTimeNoSeconds] >= MAX ( DateTime[DateTime] )
            && 'Table'[StartTimeNoSeconds] <= MAX ( DateTime[DateTime] )
    )
)

 

Result below:

MFelix_1-1613569774018.png

For the date and hours you need to turn of the concactenate values and show values without data.

 

Result in attach PBIX file.


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

5 REPLIES 5
Julver
Frequent Visitor

thanks so much @MFelix , I had  2 results:

A.

Julver_0-1613592539791.png

 

B.

Julver_1-1613592593720.png

 

Is it possible to show in graph B, fill the same color as the legend of graph A?

Hi @Julver  when you say to have the same colours what do you mean to have the colours of the no programado and programado based on equipo?

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Julver
Frequent Visitor

Please, how I can configurate to start the report at 7:00 am and finish 07:00 an from the next day?

Julver
Frequent Visitor

exactly, colours of the no programado and programado based on equipo.

MFelix
Super User
Super User

Hi @Julver ,

 

First you need to create a calendar table with date time something simlar to this:

 

DateTime = 
ADDCOLUMNS (
    CROSSJOIN (
        CALENDAR ( DATE ( 2020, 1, 1 ), DATE ( 2020, 12, 31 ) ),
        UNION (
            ROW ( "Time", TIME ( 1, 0, 0 ) ),
            ROW ( "Time", TIME ( 2, 0, 0 ) ),
            ROW ( "Time", TIME ( 3, 0, 0 ) ),
            ROW ( "Time", TIME ( 4, 0, 0 ) ),
            ROW ( "Time", TIME ( 5, 0, 0 ) ),
            ROW ( "Time", TIME ( 6, 0, 0 ) ),
            ROW ( "Time", TIME ( 7, 0, 0 ) ),
            ROW ( "Time", TIME ( 9, 0, 0 ) ),
            ROW ( "Time", TIME ( 10, 0, 0 ) ),
            ROW ( "Time", TIME ( 11, 0, 0 ) ),
            ROW ( "Time", TIME ( 12, 0, 0 ) ),
            ROW ( "Time", TIME ( 13, 0, 0 ) ),
            ROW ( "Time", TIME ( 14, 0, 0 ) ),
            ROW ( "Time", TIME ( 15, 0, 0 ) ),
            ROW ( "Time", TIME ( 16, 0, 0 ) ),
            ROW ( "Time", TIME ( 17, 0, 0 ) ),
            ROW ( "Time", TIME ( 18, 0, 0 ) ),
            ROW ( "Time", TIME ( 19, 0, 0 ) ),
            ROW ( "Time", TIME ( 20, 0, 0 ) ),
            ROW ( "Time", TIME ( 21, 0, 0 ) ),
            ROW ( "Time", TIME ( 22, 0, 0 ) ),
            ROW ( "Time", TIME ( 23, 0, 0 ) ),
            ROW ( "Time", TIME ( 24, 0, 0 ) )
        )
    ),
    "DateTime", [Date] + [Time],
    "Hour", HOUR ( [Time] )
)

 

Now create the following measure:

 

Count based on hours =
CALCULATE (
    COUNT ( 'Table'[EQUIP] ),
    FILTER (
        'Table',
        'Table'[FINISH TIME] >= MAX ( DateTime[DateTime] )
            && 'Table'[START TIME] <= MAX ( DateTime[DateTime] )
    )
)

This gives the result below using the Date time has an axis and the measure in values:

 

MFelix_0-1613569504268.png

Looking at your picture seems to me that you are not considering the seconds in each of the start and finish so I created two new columns:

FinishTimeNoSeconds = MROUND ( 'Table'[FINISH TIME], TIME ( 1, 0, 0 ) ) + TIME ( 0, 0, 0 )

StartTimeNoSeconds = MROUND ( 'Table'[START TIME], TIME ( 1, 0, 0 ) ) + TIME ( 0, 0, 0 )

This removes the seconds from the start and finish now you just need to redo your measure to:

 

Count based on hours no seconds = 
CALCULATE (
    COUNT ( 'Table'[EQUIP] ),
    FILTER (
        'Table',
        'Table'[FinishTimeNoSeconds] >= MAX ( DateTime[DateTime] )
            && 'Table'[StartTimeNoSeconds] <= MAX ( DateTime[DateTime] )
    )
)

 

Result below:

MFelix_1-1613569774018.png

For the date and hours you need to turn of the concactenate values and show values without data.

 

Result in attach PBIX file.


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



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.