Stacked Bar Graph from time duration column

[mazzurri]

I am currently capturing my team's  daily activity. I have a query for time duration of each activity (end time - start time). Right now I am plotting total duration (00:00 - 23:59). I would like to filter so that I have duration from 00:00 - 11:59 as the day shift and 12:00 to 23:59 as the night shift. I would like them to be stacked bar graphs as well. Would someone be able to provide me insight on how to go about solving this?

The bar graph is currently showing total duration and the line graph is showing total logged ticket count for the day. I would like to be able to filter them for the day/night shift and have them on the same graph.

Thank you

Mario

[sturlaws]

Hi, @mazzurri 

without knowing your data, I can only do an estimated guess on what may solve your problem:

create a new calculated like this:

column =
IF (
    table[start time] >= TIME ( 0, 0, 0 )
        && table[start time] <= TIME ( 11, 59, 0 ),
    "Day shift",
    "Night shift"
)

It seems like you are quite new here in the forum, welcome. Here is a link to a post with a lot of tips for how to getting high quality answers when you post in this forum: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

Cheers,
Sturla

If this post helps, then please consider Accepting it as the solution. Kudos are nice too.

[mazzurri]

Thank you! That helps in getting a "shift" filter on my plot, but how would I plot them both on the same bar/line graph? I would like 2 lines (1 for day and 1 for night) and a stacked bar graph for duration (day and night stacked on each other).

[v-lili6-msft · Accepted solution]

hi @mazzurri 

For your case, you could try this way:

Create two day /night measure for Duration and  two day /night measure for Ticket Count

For example:

DAY shift Duration = CALCULATE(SUM('Table 1'[Duration]),FILTER('Table 1','Table 1'[SHIFTCOLUMN]="DAY shift" ))

Night shift Duration = CALCULATE(SUM('Table 1'[Duration]),FILTER('Table 1','Table 1'[SHIFTCOLUMN]="Night shift" ))

Day shift Ticket Count = CALCULATE(SUM('Table 1'[Ticket Count]),FILTER('Table 1','Table 1'[SHIFTCOLUMN]="Day shift" ))

Night shift Ticket Count = CALCULATE(SUM('Table 1'[Ticket Count]),FILTER('Table 1','Table 1'[SHIFTCOLUMN]="Night shift" ))

Then put these measrue into visual

Result:

BeforeAfter

and here is sample pbix file, please try it.

Regards,

Lin

[mazzurri]

Thank you so much! That worked perfectly!

Mario