Calculate Lab days between the last date and the previous date

[Guillermox]

Hello, I want to calculate the business days between the record date of the last date by type and the previous type date in a measure that I will place in a matrix visualization filtered by teams in rows and in columns by Type. (additionally I am calculating an average of % but could not calculate the days)

Table
Team	#Type	%Result	Date
A	T1	80%	4/03/2022
A	T2	81%	9/03/2022
A	T3	79%	17/03/2022
Y	T1	78%	19/03/2022
Y	T2	85%	20/03/2022
A	T4	85%	6/04/2022
A	T5	86%	15/04/2022
A	T6	87%	22/04/2022
Z	T2	85%	1/04/2022
Z	T3	85%	6/04/2022
Y	T3	86%	25/03/2022
Y	T4	85%	26/03/2022
Z	T1	86%	25/03/2022
Z	T4	75%	13/04/2022
Z	T5	86%	18/04/2022

In a table matrix visualization, I added the measure by type so that I got something like this:

Visualization table (messure)
Team	T1	T2	T3	T4	T5	T6
A		4	7	15	8	6	#Days
Y		0	5	1			#Days
Z		6	4	6	4		#Days

How can you calculate the days? because also later validate that a certain number of days have not passed between each type.

[LukeVanLaar26]

I would create a "Types" dimension table with an index:

Relate that to your fact table:

Then write the following measure:

Cycle Time (Workdays) = 
VAR _SelectedType = SELECTEDVALUE('Table'[#Type])
VAR _SelectedTypeIndex = LOOKUPVALUE(Types[Index],Types[Type],_SelectedType)
VAR _PreviousTypeIndex = _SelectedTypeIndex - 1
VAR _FirstDate = LASTDATE('Table'[Date])
VAR _SecondDate = CALCULATE(LASTDATE('Table'[Date]),ALL('Table'[#Type]),Types[Index] = _PreviousTypeIndex)
VAR _Result = NETWORKDAYS(_FirstDate,_SecondDate)
RETURN
IF(ISBLANK(_SecondDate),0,_Result)

Put that on your matrix:

Hope that works for you!

Connect on LinkedIn

[ryan_mayu]

@Guillermox 

could you pls explain the calculation logic?

[Guillermox]

Hello @ryan_mayu , I need to calculate the number of days from the most recent record date vs the previous record date segmented by the "Type" column. Example: Date T7 - Date T6, Date T6 - Date T5 ...Date T2 - Date T1. But accept the context filters and external filters since my table has many more columns like teams, sub team, dimension, and external filters like Roles, Period, etc.
To then show the user a matrix table where I place the team, sub team, %, Type Date, and the days between each registration date.
In my image I want to add that column in my matrix table showing the calculated measure.

Thanks.

[ryan_mayu · Accepted solution]

maybe you can try this

Measure = 
VAR _c=max('Table'[Date])
VAR _l=maxx(FILTER(all('Table'),'Table'[Team]=max('Table'[Team])&&'Table'[Date]<max('Table'[Date])),'Table'[Date])
return if(ISBLANK(_c)||ISBLANK(_l),BLANK(),NETWORKDAYS(_l,_c))

pls see the attachment below

[Guillermox]

Thank you very much, this worked best for me.

[ryan_mayu]

you are welcome