Dynamic Average Aging based on DATEDIFF between column date and selected date value

[hungryhippos1]

Hi all, I'm trying to figure out how to find the average of a dynamic datediff between a column date field and a selected date value.

The idea would be that there is two tables, one that is at an issue level granularity like the following:

Category	issue id	startdate
IT	1	1/1/2020
IT	2	2/2/2020
HR	3	3/1/2020
HR	4	1/4/2020

And another that would be a table that has various dates that will be used for slicing purposes.

The idea of how this would work is that I would be able to find the DATEDIFF (Days) of each issue id based on the selected date value chosen. From there, I would then aggregate the datediff via an average at the Category level. Something along the lines of:

=AVERAGE(DATEDIFF(Table1[startdate],SELECTEDVALUE(Table2[date]),DAY))

The big problem is that I don't know how I would properly input the startdate into the measure and get a proper datediff for each row to have it then rollup for the average. Any help would be greatly appreciated!

[v-robertq-msft · Accepted solution]

Hi, @hungryhippos1 

According to your description, you want to calculate the dynamic average value of datediff between the [startdate] and the selected date in Slicer, you can follow my steps:

1. Create these two measures:

Datediff =

var _selecteddate=SELECTEDVALUE(Table2[Date])

var _datediff=DATEDIFF(MAX('Table1'[startdate]),_selecteddate,DAY)

return

_datediff

Average of datediff = AVERAGEX(values('Table1'[startdate]),[Datediff])

2. Create a Slicer and place ‘Table2’[Date], a table and place columns like this:

And you can get what you want, like this:

You can download my test pbix file here

Best Regards,

Community Support Team _Robert Qin

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