cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## Measure in matrix returns values for blank years despite date table

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?

7 REPLIES 7
Frequent Visitor

@lbendlin did you have any solution for this issue?

Frequent Visitor

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])

Super User
``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?

Frequent Visitor

@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

Super User

Please show the expected outcome based on the sample data you provided.

Frequent Visitor

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
Super User

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-...

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

#### Fabric Community Update - September 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors