Sum of Measure by Month

[Anonymous]

Hi, I have data that looks like this (basically door access log):

AccessLog

Date| Time | Event | User

1 Jan | 0400 | Access Logged | Jane

1 Jan | 0600 | Access Logged | Jane

1 Jan | 0700 | Access Logged | Jane

1 Jan | 0200 | Access Logged | Bob

1 Jan | 0800 | Access Logged | Bob

1 Jan | 1200 | Access Logged | Bob

2 Jan | 0400 | Access Logged | Jane

2 Jan | 0600 | Access Logged | Jane

2 Jan | 0700 | Access Logged | Jane

2 Jan | 0200 | Access Logged | Bob

2 Jan | 0800 | Access Logged | Bob

2 Jan | 1200 | Access Logged | Bob

I am able to get the hours spend in office each day using:

Hours Worked = (DATEDIFF(FIRSTNONBLANK(AccessLog[Time],1),LASTNONBLANK(AccessLog[Time],1),MINUTE))/60

within a Matrix report with Row = Date, Column = User, Value = Hours Worked

Now, I need to calculate the hours work per user, per month.

How can I sum the above 'Hours Worked' measure, by month for each user?

[v-yuta-msft · Accepted solution]

Hi powerbi9191,

To achieve your requirement, you can create a measure using dax formula below:

Diff = CALCULATE((MAX(AccessLog[Time]) - MIN(AccessLog[Time])) / 60, ALLEXCEPT(AccessLog, AccessLog[Date], AccessLog[User]))

 

Regards,

Jimmy Tao

[Greg_Deckler]

Create a Month column using something like:

Month = RIGHT([Date],3)

If it is an actual Date/Time column:

Month = FORMAT([Date],"mmm")

You will also need a MonthSort column most likely. If it is a date/time column:

MonthSort = MONTH([Date])

If it is a text column, then you will need a Switch statement most likely.