Number of Open Issues

Hi there,

I'm thinking about changing from eazyBI to Power BI.

And, there is one measure, Open Issues, that I'm particularly interested in. Basically, it includes those tickets that have created date but do not have a resolved date, and I would like to keep a record for every day (i.e. I can know how many open issues were on 1/1/2019 or 31/3/2016 etc.)

In eazyBI, the measure is defined as:

CASE WHEN [Issue].CurrentMember.Level.Name <> 'Issue' THEN
Cache(
NonZero(Sum(PreviousPeriods([Time].CurrentHierarchyMember),
Cache([Measures].[Issues created]
- [Measures].[Issues resolved])
))
+ [Measures].[Issues created]
- [Measures].[Issues resolved]
)
WHEN [Time].CurrentHierarchyMember IS [Time].CurrentHierarchy.DefaultMember
THEN NonZero([Measures].[Issues due])
ELSE
-- optimized formula for drill through Issue
NonZero(IIF(
DateBeforePeriodEnd(
[Issue].CurrentMember.get('Created at'),
[Time].CurrentHierarchyMember) AND
NOT DateBeforePeriodEnd(
[Issue].CurrentMember.get('Resolved at'),
[Time].CurrentHierarchyMember),
([Time].CurrentHierarchy.DefaultMember,
[Measures].[Issues created]),
0
))
END

I'm wondering how it can be defined in Power BI.

Thanks and have a good day.

v-piga-msft v-yuta-msft v-yulgu-msft