How to calculate a 12 hr average from 6pm to 6am.

[REdwards]

I am trying to calculate a shift average.  I have two shifts each day.  A DAY and NIGHT shift.

on Monday, the day shift is 6:00 am Monday to 6:00 pm Monday.  The night shift is 6:00pm Monday to 6:00am Tuesday...

Any suggestions on how to build this so i can get two averages each date stamp with the proper hours included?

[REdwards]

What I am trying to do is get Power BI to properly group my day and night shift data.  It does ok on the day shift, since day shift for 4/1/19 is 6:00 - 17:00.  However night shift is 4/1/19 18:00 - 4/2/19 5:00.  When i try grouping by date it only includes data with a 4/1/19 date stamp and ignores the 6 hrs on the next day.

[v-cherch-msft]

Hi @REdwards 

You may create the measures like below,

Day shit =
AVERAGEX (
    FILTER (
        Table1,
        Table1[Time] >= TIMEVALUE ( "6:00" )
            && Table1[Time] <= TIMEVALUE ( "17:00" )
    ),
    Table1[Value]
)

Regards,

[REdwards]

This is still not working.  I had to do some modification to the date, since the data does't pull in on the exact hr.  I believe all formatting is correct but I am getting a blank in the Day Shift column (name slightly modified from suggestion above :))

thanks

 Also, I might add as you can see here, each hr may have multiple values and i have multiple days as well.

[v-cherch-msft]

Hi @REdwards 

Please check the attached sample file and you may create two measures to get the average.

Regards,

[REdwards]

thanks again... but I was able to get this far... please see attached photo  I brought your data into Excell to show what I am trying to duplicate.  Yes, the attached answer is averaging day and night shift data... However, as shown below, our answer are different.  This is because the the night shift is split across two different data.

Your 4/1/19 night shift is averaging 4/1/19 0:00 - 4/1/19 5:00 AND 4/1/19 18:00 - 4/1/19 23:00.

The 4/1/19 night shift runs from 4/1/19 18:00 - 4/2/19 5:00.  Each shift is 12 consecutive hours.  The day shift average is correct.  it is the night shift I ma having a hard time duplicating.

[v-cherch-msft · Accepted solution]

Hi @REdwards 

You may add a new column as below and then the formula could be correct.

Column 2 =
IF (
    Table1[Time] >= TIMEVALUE ( "6:00" )
        && Table1[Time] <= TIMEVALUE ( "23:00" ),
    Table1[Date.1],
    IF ( Table1[Time] <= TIMEVALUE ( "5:00" ), Table1[Date.1] - 1 )
)

Regards,

[REdwards]

I should mention that I am pulling data in from an external link every 5 minutes.

[v-cherch-msft]

Hi @REdwards 

Sample data and expected output will be helpful to provide an accurate solution.If you need further help,please follow the How to Get Your Question Answered Quickly to post your data and expected output.It would be better if you can upload the .pbix file to OneDrive and post the link here. Do mask sensitive data before uploading.

Regards,