DAM measure

[prashantg364]

Hi All,

i wan to calculate the number of days spent against each workflow.

There will be multiple rows of same Workflow in the data set based on number of time woid has been executed. Woid column is unique values.

for each workflow I need to calculate the end date-start date

for eg:

workflow name: 

 Performance Analysis Multi

min of start date is 18-05-23

max of end date is 07-06-23

total spent days =  07-06-23 - 18-05-23 = 20 days

woid	WO_NetworkElementName/ID	workflowname	Start Date	End Date
218354299	M0096	Pre Swap KPI Preparation Multi	15-05-2023	15-05-2023
218354301	M0096	Pre Swap KPI Preparation Multi	16-05-2023	16-05-2023
218495911	M0096	AUDIT	17-05-2023	17-05-2023
218471333	M0096	Full Health Check Multi	16-05-2023	16-05-2023
218514715	M0096	hourly KPI report preparation Multi	18-05-2023	18-05-2023
218495923	M0096	hourly KPI report preparation Multi	17-05-2023	17-05-2023
218471356	M0096	hourly KPI report preparation Multi	16-05-2023	16-05-2023
218621069	M0096	Performance Analysis Multi	24-05-2023	24-05-2023
218796428	M0096	Performance Analysis Multi	03-06-2023	03-06-2023
218514691	M0096	Performance Analysis Multi	18-05-2023	18-05-2023
218776601	M0096	Performance Analysis Multi	02-06-2023	02-06-2023
218867723	M0096	Performance Analysis Multi	07-06-2023	07-06-2023
218580207	M0096	Final Acceptance Report U BOT Multi	22-05-2023	22-05-2023
218810065	M0096	Final Acceptance Report U BOT Multi	05-06-2023	05-06-2023

[Anonymous · Accepted solution]

Hi @prashantg364 

You can refer to the following measure

DAM =
VAR a =
    FILTER (
        ALLSELECTED ( 'Table' ),
        [workflowname] IN VALUES ( 'Table'[workflowname] )
    )
VAR b_mindate =
    MINX ( a, [Start Date] )
VAR b_maxdate =
    MAXX ( a, [End Date] )
RETURN
    DATEDIFF ( b_mindate, b_maxdate, DAY )

Output

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[prashantg364]

it's helps. Thanks for the suggestion.