Number of Open Issues

[Syne97]

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 

[TeigeGao]

Hi @Syne97 ,

Could you please share the sample data and expected result to us for analysis? It's not easy to analysis your requirement without samples.

Best Regards,

Teige

[Syne97]

Hi @TeigeGao,

Sure! For example, the business starts on 1/1, and the number of issues created is 5, and we resolved 3; on 2/1, the number of new issues created is 9, and 9 + (5 - 3) = 11, and we resolved 7 on this day.

So on 1/1, the open issue number is 2, and on 2/1, the open issue number is 4. And, I would like to have a line graph.

Thanks and have a good day.