Calculating number of days worked?

[amalm]

I have my data in the following format:

I need my visual in the following format. I already have measures defined for total hours worked per day, I'm just having a bit of trouble with the number of days worked.

Thanks a lot.

[v-lili6-msft]

hi  @amalm 

If there is a working time limit to determine if it's a worked day?

If not, Just create a measure as below:

Measure = DISTINCTCOUNT(Table[Date])

if yes, please share the logic of it.

Regards,

Lin

[amalm]

Thank you, what if I want to have 2 measures Fulldays and Halfdays where anything less than total 6 hours is considered a half day?

[v-lili6-msft · Accepted solution]

hi  @amalm 

If so, create two measure as below:

Fulldays = 
var _table= SUMMARIZE('Table','Table'[username],'Table'[date],"hours",CALCULATE(SUMX('Table',DATEDIFF('Table'[start time],'Table'[end time],MINUTE)))/60)
 return
COUNTAX(FILTER(_table,[hours]>=6),[date])

Halfdays = 
var _table= SUMMARIZE('Table','Table'[username],'Table'[date],"hours",CALCULATE(SUMX('Table',DATEDIFF('Table'[start time],'Table'[end time],MINUTE)))/60)
 return
COUNTAX(FILTER(_table,[hours]<6),[date])

Regards,

Lin

[amitchandak]

@amalm , what is role of time for this.

You can use a measure  distinctcount(Table[Date])

[nandukrishnavs]

@amalm 

If there is no additional logic for considering a date as a working day, you can calculate the distinct count of the date column.

Example

Username	date
user1	12 April 2020
user1	12 April 2020
user2	12 April 2020
user2	12 April 2020
user3	12 April 2020
user1	13 April 2020

Measure = DISTINCTCOUNT(MyTable[date])

 

Did I answer your question? Mark my post as a solution!
Appreciate with a kudos 🙂

[az38]

Hi @amalm 

smth like

days worked measure = 
CALCULATE(SUMX(Table, ([end time] - [start time])/24) )