Average Between Three Dates Versus Main Date

[Anonymous]

I am creating a dashboard to show the days between(Lag) the latest of three different date columns versus the main date(Physical Date) column.

Now I need to find the average days between those three different date columns versus the main date(Physcial Date) column and present it in a visual like a bar or line chart. 

Example: Product Order was placed and shipped(Physical Date)and it goes into either three different date column flows:

1) Submission Date(Meaning it is the normal price and it goes straight to invoicing)

2) Price Approved Date(The product has a special price so that price needs to get approved before it goes to invoicing)

3) Terms Approved Date(The product price has different terms than normal so that needs to be approved)

They want to know how many days it took to move between main date(Physical Date) to Price approved date, or between Main Date (Physical Dat)e to submission date etc. Currently I am using a calulcated column to find the last date of the three dates compared to the main date(Physical Date) in days.

DaysBetween Lag Column = VAR _pDate = SELECTEDVALUE('TABLE1'[Physical Date]) VAR _paDate = SELECTEDVALUE(Table1[PriceApproved.date]) VAR _taDate = SELECTEDVALUE(Table1[Terms Approved Date]) VAR _sDate = SELECTEDVALUE('Table1'[Submission.date]) VAR _maxDate = MAX(MAX(_paDate, _taDate), _sDate) RETURN DATEDIFF(_pDate, _maxDate, DAY)

That column displays in a table like this:

They are wanting to group by the latter of the three dates (Price Approved Date, Submission Date, Terms Approved Date) versus physical Date and then get the average of that. The average part is what I am needing help with. Any suggestions please?

Data Table is just sample data.  Please Note: The three dates (Submission Date, Price Approved Date and Terms Approved Date) will not always have a value depending on what "flow" the item went into it after it was shipped.

@Dangar332 or anyone else...Any suggestions Please? Thank you in advance!

Product ID	Physical Date	Submission Date	Price Approved Date	Terms Approved Date
24567	1/4/2024	1/7/2024
676742	1/6/2024		1/10/2024
42516	1/15/2024			1/26/2024

[Anonymous · Accepted solution]

Hi @Anonymous 

Maybe you can try this:
I added some date into the sample data to test:

Then add 3 calculate columns to get the Datediff of the 3 type date,like this:

Then i create a table:

And a measure:

Lag1 = 
VAR _aveValue = SELECTEDVALUE('Table'[Value])
	VAR result = SWITCH(
		_aveValue,
		"ave_sDate", CALCULATE(
			SUM('Table (2)'[SDATE]) / COUNTROWS('Table (2)'),
			FILTER(
				ALLSELECTED('Table (2)'),
				'Table (2)'[Submission Date] <> BLANK()
			)
		),
//Get the average of the sum of the datediff from physical date to submission Date
		"ave_paDate", CALCULATE(
			SUM('Table (2)'[PADATE]) / COUNTROWS('Table (2)'),
			FILTER(
				ALLSELECTED('Table (2)'),
				'Table (2)'[Price Approved Date] <> BLANK()
			)
		),
		"ave_taDate", CALCULATE(
			SUM('Table (2)'[TADATE]) / COUNTROWS('Table (2)'),
			FILTER(
				ALLSELECTED('Table (2)'),
				'Table (2)'[Terms Approved Date] <> BLANK()
			)
		)
	)
	RETURN
		result

The result is as follow:

Best Regards,

Zhengdong Xu

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

[lbendlin]

What's the expected outcome based on the sample data you provided?