How to timeline display from start to end of batch (Show the time when the batch process is running)

[Lopez0090]

▷ What you want to achieve

I want to create a table like the one below using a matrix visual or a table visual (or a custom visual).

This is the batch processing time (from start to finish) for each system.

The color is darker for the time that is executed a lot, and lighter for the time that is not executed much.

▷ Data source sample

If anyone knows how to do that, please let me know.

Thank you.

[Icey]

Hi @Lopez0090 ,

Please check the attached .pbix file.

TimeTable =
SELECTCOLUMNS (
    GENERATESERIES ( 0, 23 ),
    "Time", CONVERT ( [Value] & ":00", DATETIME )
)

Measure = 
CALCULATE (
    COUNT ( 'Table'[Batch_ID] ),
    FILTER (
        ALLEXCEPT ( 'Table', 'Table'[System] ),
        'Table'[Time(S)]
            < MAX ( TimeTable[Time] ) + TIME ( 1, 0, 0 )
            && 'Table'[Time(E)] > MAX ( TimeTable[Time] )
    )
)

Best Regards,

Icey

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

[Lopez0090]

@Icey 

Thank you very much.
I have checked your report and it is perfect.

Please tell me one.

I'm counting all the "Batch_IDs" now, but if the year is different, can I display the counted values for each year?

I added a "Year" column, but the slicer did not allow me to narrow it down.

Best Regards,

Lopez

[Icey]

Hi @Lopez0090 ,

Do you mean that when you open my .pbix file, the Time column shows as "Date"?

If so, could you try to re-create the time table and change the data type to "Time" manually? And then check if the issue is still there.

Or you may try to use M language to create a Time table. Please refer:

Script for Creating TIME Table in Power BI with Hours, Minutes and Seconds Buckets - RADACAD;

Creating a Time Dimension using the Power Query Editor in Power BI Desktop - Reporting/Analytics Mad...

Of couse, just using "enter data" is OK.

If all above don't work, Which version of Power BI Desktop are you using? Could you update to the latest one (version: 2.94.921.0) and test it again?

Best Regards,

Icey

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

[Lopez0090]

@Icey 

Thank you!

I recreated the time table and manually changed the data type to "Time" and it was fine.

I'm sorry,,, I changed my question without knowing that you had replied.

I'm counting all the "Batch_IDs" now, but if the year is different, can I display the counted values for each year?

 

[Icey]

Hi @Lopez0090 ,

Try to modify the measure like so:

Measure =
CALCULATE (
COUNT ( 'Table'[Batch_ID] ),
FILTER (
ALLEXCEPT ( 'Table', 'Table'[System], 'Table'[Year] ),
'Table'[Time(S)]
< MAX ( TimeTable[Time] ) + TIME ( 1, 0, 0 )
&& 'Table'[Time(E)] > MAX ( TimeTable[Time] )
)
)

Best Regards,

Icey

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

[Lopez0090]

@Icey 　

Thank you so much.

I was able to display the data by year.

Sorry, Can I ask one last question?
I wish I could do this, but I would like to add another color if I go over the allotted time, is that possible?

For example, in System B, if it's past 8pm, For System A, if the count is after 21:00, the color is red.

Best Regards,

Lopez

[Icey]

Hi @Lopez0090 ,

I don't find a direct way, but there is a workaround. Please check if this could meet your requirements:

Steps:

Best Regards,

Icey

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

[Lopez0090]

@Icey 

I apologize for the delay in responding.

Thank you very much. I found that it was difficult to achieve.

If I remove the gradient, can I do it?

[Icey · Accepted solution]

Hi @Lopez0090 ,

If the gradient is removed, just modify the previous "Measure" like so:

Measure =
VAR Count_ =
    CALCULATE (
        COUNT ( 'Table'[Batch_ID] ),
        FILTER (
            ALLEXCEPT ( 'Table', 'Table'[System] ),
            'Table'[Time(S)]
                < MAX ( TimeTable[Time] ) + TIME ( 1, 0, 0 )
                && 'Table'[Time(E)] > MAX ( TimeTable[Time] )
        )
    )
RETURN
    IF (
        Count_ > 0,
        IF ( MAX ( TimeTable[Time] ) > TIME ( 20, 0, 0 ), "Red", "Blue" )
    )

Or, just create a new measure based on the previous "Measure":

Measure 2 =
IF (
    [Measure] > 0,
    IF ( MAX ( TimeTable[Time] ) > TIME ( 20, 0, 0 ), "Red", "Blue" )
)

Then set conditional formatting:

Best Regards,

Icey

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

[Lopez0090]

@Icey 

That was so helpful!
Thank you very much for everything!