Average of min(date) with set analysis using dimension

[SkitzN27]

Hi

Here's my data 

The calculation for my Days column is = 'Date 1 - min({<Dimension={'XYZ'}>}Date2)' . Now I want to take an Average(Days) i.e. '(Average(Date 1 - min({<Dimension={'XYZ'}>}Date2)) 'and present it on bar chart with Yr Qtr of Date1 as my dimension.

Dimension: Yr Qtr of Date 1

Expression: Avg(Date 1 - min({<Dimension={'xyz'}>}Date2))

For example: The average for 2020 Q1 = (54+33+48)/3 = 45

The average of 2020 Q2 = (145+28)/2 = 86.5

@QFabian @MayilVahanan @rwunderlich 


Sample Data:

Unique Number	Phase	Days	Date1	Yr Qtr of Date 1	Dimension	min({<Dimension={'XYZ'}>}Date2)
1	Phase 1	-	-	-	xyz	01-Oct-2020
2	Phase 1	145	29-Jun-2020	2020 Q2	xyz	04-Feb-2020
3	Phase 1	54	30-Mar-2020	2020 Q1	xyz	04-Feb-2020
4	Phase 1	33	09-Mar-2020	2020 Q1	xyz	04-Feb-2020
5	Phase 1	-	-	-	xyz	15-Oct-2020
6	Phase 1	-	-	-	xyz	01-Oct-2020
7	Phase 1	-	-	-	xyz	01-Oct-2020
8	Phase 1	28	04-Jun-2020	2020 Q2	xyz	06-May-2020
9	Phase 1	48	24-Mar-2020	2020 Q1	xyz	04-Feb-2020

[SkitzN27]

The last column is date 2, I have just modified it using this formula: min({<Dimension={'XYZ'}>}Date2)

@QFabian 

[Kushal_Chawda]

@SkitzN27  try below

=Avg(aggr(Only(Date1)-min({<Dimension={'XYZ'}>}Date2),Date1,[Yr Qtr of Date 1]))

[Kushal_Chawda]

@SkitzN27  or may be below. 

=Avg(aggr(Only(Date1)- min(if(Dimension='xyz',Date2)),Date1,[Yr Qtr of Date 1]))

[MayilVahanan · Accepted solution]

Hi @SkitzN27 

Try like below

In bar chart.
Avg(Aggr(Date1 - [min({<Dimension={'XYZ'}>}Date2)], [Yr Qtr of Date 1], [Unique Number]))

or
In Straight table
Avg(Total<[Yr Qtr of Date 1]> Aggr(Date1 - [min({<Dimension={'XYZ'}>}Date2)], [Yr Qtr of Date 1], [Unique Number]))

[SkitzN27]

Thank you so much @MayilVahanan