Measure in matrix returns values for blank years despite date table

[j1s]

I need to display in a matrix by year the average number of days between two date fields (for records where both dates exist). 

The results I'm expecting are these:

2023  2022  2021  Total

0.9   2.0   8.4  2.89

...calculated using Excel formulae like this for 2023:

=AVERAGEIFS(q_compliance[Days to Action Plan from Risk Assessment],
q_compliance[Action Plan after latest assessment],TRUE,
q_compliance[Risk Assessment Completed Date],">0",
q_compliance[Action Plan Complete Date],">0",
q_compliance[Action Plan Status],"Finished",
q_compliance[Days to Action Plan from Risk Assessment],">=0",
q_compliance[Risk Assessment Completed Date],">="&V7,
q_compliance[Risk Assessment Completed Date],"<"&V8)
)

In the above V7 is the date of the first date in the year (01 Jan 2023), and V8 the first day of the next year (01 Jan 2024)  

So for the DAX measures, I tried 3 ways, all of which produce the wrong figures as below:

AverageX without Userelationship =
AVERAGEX(
FILTER(q_Compliance,
q_Compliance[Action Plan Complete Date] >= q_Compliance[Risk Assessment Completed Date] &&
q_Compliance[Risk Assessment Completed Date] <> BLANK() &&
q_Compliance[Action Plan Complete Date] <> BLANK() &&
q_Compliance[Action Plan Status]="Finished" &&
q_Compliance[Days to Action Plan from Risk Assessment] >=0 ),
q_Compliance[Days to Action Plan from Risk Assessment]
)

Calculate Average without Userelationship =
CALCULATE(
AVERAGE(q_Compliance[Days to Action Plan from Risk Assessment]),
FILTER(q_Compliance,
q_Compliance[Action Plan Complete Date] >= q_Compliance[Risk Assessment Completed Date] &&
q_Compliance[Risk Assessment Completed Date] >= MIN('Calendar'[Date]) &&
q_Compliance[Risk Assessment Completed Date] <= Max('Calendar'[Date]) &&
q_Compliance[Action Plan Complete Date] <> BLANK() &&
q_Compliance[Action Plan Status]="Finished" &&
q_Compliance[Days to Action Plan from Risk Assessment] >=0 )
)

Calculate Average with Userelationship =
CALCULATE(
AVERAGE(q_Compliance[Days to Action Plan from Risk Assessment]),
FILTER(q_Compliance,
q_Compliance[Action Plan Complete Date] >= q_Compliance[Risk Assessment Completed Date] &&
q_Compliance[Risk Assessment Completed Date] >= MIN('Calendar'[Date]) &&
q_Compliance[Risk Assessment Completed Date] <= Max('Calendar'[Date]) &&
q_Compliance[Action Plan Complete Date] <> BLANK() &&
q_Compliance[Action Plan Status]="Finished" &&
q_Compliance[Days to Action Plan from Risk Assessment] >=0 ),
USERELATIONSHIP('Calendar'[Date],q_Compliance[Risk Assessment Completed Date])

Where am I going wrong?

[j1s]

@lbendlin did you have any solution for this issue?

[j1s]

Here is an example of 500 rows (anonymised) for each column in the table from which the measures are derived:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("7VrLih03EP2V4LUbStUvaWkwXhjygGRnvHDswR4wY5PEgfx9dLuv1NVVR031rBtmcTldI9VL9ZLevXvx4uWL3x6ePj0+fc6/bn/vX17gBV7gBV7gBV7gBV7gBV7gBV7gBTbAELqfP/zXcZ+BN49Pj39/efiUf4Z5g6eVcO7e/vhqCHno3n54WuEuzMNCS9y9+vG5Y97TUup++fZvx+FGy9N93bF78/CnXTdlBv5a4YHXVYfu94fvhpLitlnXp0r7+uGjFesu7cJB6ueFlufu14//WNq4CrysO9y5pZsWniy3YROiC3SnnbbNJL/DukRYpCz6WoTV+hpXxhZuwxBXfdGmREk7r3a4LTGulNyvlGx4ffX9vlle5s5raq1aGQshFlJk3MxXhcPKKUOPCf0Gr4RZTmTYnqRh5/uiw8qoNkDcGO3GtAp1k/S2gKad1+3WdTkWZhEPZbuVllbnCgkLRhvcpeJaFfrj20+vv8mTN2w+o79ljVT31d84bVLpb33YTqPZL21ubvbrD/ictlBgvvWrLzPmpZrP/N/9yMFvafNPI7sws/k2t2XP8jV1zfMWacy3of2NeAsFQL62zmLbDjkYNm2bY9eRjdprHv3f0bcDXnJoa/vudKCzsa2XHK6OvjV1zSItABkW+QLiJbS/cdxitPnGB/55l6Hhn20fPDhHMuCDGFLDk1nzHufP2lbmMvONDuwQDtbsD2JPONAnt2Xng/gZDs4t84Ftj/Q5HpwVasuX9bLEcpuGK0wr4bTlJUkoYNoldpWsJUwlPFd33GfgClPxd8hj1DzKgLGvqypMJiJJQgGvhCXLN5J/L6WGpZeA9+qxhBWmwwKFjTBuwrTVjJJQwGf1GHBdImAqblZDyN7NKky7+k2X0Ukr/AyPUOqgpeaIC7Joto54xWj06LWMrACk1ALem1CvmKCtrT9KmEovAhU+GKm9Z0aGm30Bqgln2IJImEpirslw34FU+E4YV8fThAKubrZo1vaAFaYSV1EHKuGz/thDHiVcnaKmyL1TVLgqHAkj4arHWtiCTs46rl6RzZmZtvS4zwoVrocLbS3h6mYoPkq42hq2pQKmksIhYdCEjSGChKkUKDDsjSDsQfUEox7MIxsec3+FvEfCVWro4UE7RRYPdswCJlPONZpQ2vpKkAtnkwsJC0NamD7AUkHC9RTC2BNM7JnwmZm0enKqR5aR8MlTWNpuLfWso5ms5SUhG+/BSZNN0iwVsl6RdBqWU4jGcKLyCAlZE+b4hoo4CZ9UTwjbUElF3A87YWQ/sT+uSj1yptMY9ZyMj8s4zYYUCZ+sHwOeM0pYRDOgnlnbOjMDs8KgQ/MZ70HqkTCVKQWse1jXPSWhaPVMJhdO2NbT88sZN6FX4ZlrqJ7JqGeEh0vC+7Bnj4LOrtjWwdg6iFkdOFw7x63N7N5xK1zjI7SMaaXcCpd9+35rReguCxnXFGxqCk54ep10LpRT8sbwvAZSlD4kXHmsE4I9jxWuh2sJrXprAdetodRsUtyES9fJFB8TPAoSrltDYVgLEwgSSrjWPVDhQSucIlZP1Oq5XeGANCzhGvZgvp4N4YCj2aCj2VJlgLqHdN1DCUeKZPK1d4xDCW4t4ZMe7tZj6ets2LMdu++43m4QQDST8FnCESf2USd2f/GBS9felq4Rz80isIy7bXYehTvXdsUKV++BN3ZJDwFz8QDPDGmnKOMDW16rzOUuZwgfBbITzYBHdkG3pJxwNEsmmg3QMhKuPMLDNZnD5T6FM6yaJbxFXFRekyb0hz3vENBdhy83YHaCJOGTDWRxE3strvWY8FQhmaSZoHokfLIszPENFh8RNOJQPXbAhkfxZEfxWI9s9MgjdooRDIYgj1HzGHA0CzaaDdjDh2d7uHxosD8zytZuN2PcQLJpIP0t6YSzwqS3Lq2QLeJUDA8zHmjMZqzoLRUYZ1c22TV7KIy4sxkCjvAljYSrCeE4zAhTSk9kmSfjuJ7iIwx48jHoyYc7u2afR9lVwlsX57r78Edcb5DyX5J4h6mErzTIXGkUZuywQF+7JOhmEt7KQpDYJVxDCrQMGctEHHuijj3+FsAbw/2WcZdc7uJjbD0l0wV74zWdnXJ57zQpYltHU8R5+2vGNS6bGtc9ay6PBvThGkzE9dpaPj9UPZc61yOuKUYz0PDOUgK++wj27sPbkoYZX0DMhkd8LU3mWprwZROZy6alygDjh9EEAMbZlU2HhGM42RjuDSnZBDDFRZDinPcKI672RpPY8SNVCVdCmF3NjDTge4Vg7xW8E80eX9D25oI2Wx92wxMYDMEGMhoecdIMz6/sacaD89k8GGi8oBbwvpWyMylFWPpje2Z0aG48WmVN6K72/J0mniCxmSDJp577kKLn4XgUH9AoHl6nBhNx3TfiM5wWSrgmJN81oPuFBhO2DJkCCUtNRmr/Ba33PYXbH4nh1hKu8dF5p+m+8Wk8du+ffa57bMLePrLB782CeW/G4sWpns58VQkJXg2R7uJCj2vc3kyQ3ENAxsKwydcDtsxgLDNAx5Vw9XBkQgmfPAr+GSl+ysHmKQfjyp5NZe+ekfpPYQ8JJbyPuDaaWceFhPvbvff/Aw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Risk Assessment Completed Date" = _t, #"Action Plan Status" = _t, #"Action Plan Complete Date" = _t, #"Days to Action Plan from Risk Assessment" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Risk Assessment Completed Date", type date}, {"Action Plan Status", type text}, {"Action Plan Complete Date", type date}, {"Days to Action Plan from Risk Assessment", Int64.Type}})
in
    #"Changed Type"

The date table is simply Calendar = CALENDARAUTO(), with year coming from Year = year('Calendar'[Date])

[lbendlin]

I need to display in a matrix by year the average number of days between two date fields (for records where both dates exist). 

by which year ? Risk assessment date year or action plan date year? 

Which way should the calculation go?  What date comes first?

[j1s]

---

@lbendlin wrote:

by which year ? Risk assessment date year or action plan date year? 

By [Risk assessment completed date] year 

@lbendlin wrote:

Which way should the calculation go?  What date comes first?

[Risk assessment completed date] comes first

---

---

---

[lbendlin]

Please provide sample data that fully covers your issue.
Please show the expected outcome based on the sample data you provided.

[j1s]

Thanks for your message. 

The expected outcome is shown in the 2nd paragraph. 

Sadly I'm not able to post confidential corporate data here, but the relevant data are:

• 2 columns containing dates between 2021-2023 (and some blanks), called [Action Plan Complete Date] and [Risk Assessment Completed Date]
• a column that is a count of the days between the dates, called [Days to Action Plan from Risk Assessment]. 
• a column called [Action Plan Status] which has a text string

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...