Calculated column that says "Yes" if someone worked the last week

[Anonymous]

Hello everybody, 

I have data with two columns, Date and Driver

Date has the first day of every week and Driver has names

Each row tells if a driver attended to work each week.

I need to generate a column to know if the driver attended to work considering the lattest week available in the field data.

There will be only dates with the first day of the week.

For example:

I need this to segment my data in the dashboard.

Thanks in advance

[TheoC · Accepted solution]

Hi @aguuzdo 

You can use the following Calculated Column:

Worked This Week = 

VAR _1 = Table[Driver]
VAR _2 = CALCULATE ( LASTDATE ( 'Table'[Date] ) , ALLEXCEPT ( 'Table' , t[Driver] ) )

RETURN

IF ( _2 = MAX ( 'Table'[Date] ) , "YES" , "NO" )

Output as per below:

In addition to the above, if you wanted to have YES only for the worker and the most recent date, then you can adjust the calculated column to the following:

Attendance = 

VAR _Name = Table[Driver]
VAR _Date = MAXX ( FILTER ( ALL ( 'Table' ) , Table[Driver] = _Name ) , Table[Date] )

RETURN

IF ( _Date = 'Table'[Date] , "YES" , "NO" )

Output looks like this:

Hope this helps!

Theo

[Ashish_Mathur]

Hi,

This calculated column formula works

=if(CALCULATE(max(Data[Date]),FILTER(Data,Data[Driver]=EARLIER(Data[Driver])))=MAX(Data[Date]),"Yes","No")

Hope this helps.

[TheoC]

Hi @Anonymous 

You can use the following Calculated Column:

Worked This Week = 

VAR _1 = Table[Driver]
VAR _2 = CALCULATE ( LASTDATE ( 'Table'[Date] ) , ALLEXCEPT ( 'Table' , t[Driver] ) )

RETURN

IF ( _2 = MAX ( 'Table'[Date] ) , "YES" , "NO" )

Output as per below:

In addition to the above, if you wanted to have YES only for the worker and the most recent date, then you can adjust the calculated column to the following:

Attendance = 

VAR _Name = Table[Driver]
VAR _Date = MAXX ( FILTER ( ALL ( 'Table' ) , Table[Driver] = _Name ) , Table[Date] )

RETURN

IF ( _Date = 'Table'[Date] , "YES" , "NO" )

Output looks like this:

Hope this helps!

Theo