Hi,
The solution requires creating custom hierarchies in the Object dimension and a highly tricky formula for counting the expired licenses per Department. As an introductory source, I recommend checking this article on how to create a report with inbound references:: Assets: How to Import "Inbound References" in eazyBI
I created a similar object schema for demonstration:
Software object has its Department and the Provider Qualification has the End of Support date field.
I imported all needed attributes:
Then I created two additional hierarchies in the Object dimension to group Objects by Derpatmetn and L_Software:
I used the Object department hierarchy in the report and created a formula to count related Provider Qualifications with expired dates:
Nonzero(Count(
Filter(
Generate(
[Object.Software Identification L_Software].[L_Software].GetMemberByKey(
[Object.Software Department].CurrentHierarchyMember.KEY
).Children,
[Object].[Object].GetMembersByKeys(
[Object.Software Identification L_Software].CurrentHierarchyMember.Get('Software Identification L_PQ')
)
),
DateCompare([Measures].[Object Provider Qualification End of Support],Now())<0
)
))
That allows creating a report like this:
Kindly,
Janis, eazyBI support