How can I create a report to compare two custom field dates?

Questions & Answers
1

Dear Community,

I am new to using this wonderful tool that is eazyBI, and I succeed creating some very useful KPIs, but I’m stuck on one report

I have a project with two custom fields dates : “Target maintenance date” and “Real maintenance date”
I would like to see the %age of issues where the Target maintenance date is after the Real Maintenance date

For this, I created 3 custom measures :

  • OTD OK : count issues where Target maintenance date is after the Real Maintenance date
  • OTD KO : count issues where Target maintenance date is before the Real Maintenance date
  • Ratio : %age of OK/(OK+KO)

It works pretty well except one thing :

image
image526×509 7.38 KB

In the previous table, Issues are linked to the month of their creation ( Jul 2020 for PM-8 for exemple ), but i would like to link them to the month of their Target Maintenance date ( Mar 2021 for PM-8, because PM-8 Target maintenance date is 15/03/2021).

Here is the code of the “OTD KO” :

Count(
Filter(
Descendants([Issue].CurrentHierarchyMember, [Issue].[Issue]),
DateDiffWorkdays(
[Measures].[Issue Planned maintenance date],
[Measures].[Issue Real maintenance date]) >= 0
AND
DateInPeriod(
[Measures].[Issue created date],
[Time].CurrentHierarchyMember )
))

I tried to change the
[Measures].[Issue created date] with [Measures].[Issue Planned maintenance date] without any success

Could you help me ?

A big thank you :slight_smile:

Antoine

2

Hi @AMAGNANI ,
Welcome to the eazyBI community!

You are on the right path about your calculated measure. But if you want to use issue dimension in rows with enabled Nonempty then your calculated measure needs to have filtered by the measure (‘Issues with planned maintenance date’), not only by the property (‘Issue Planned maintenance date’).
The formula would be like this:

Count(
  Filter(
    Descendants([Issue].CurrentMember, [Issue].[Issue]),
      [Measures].[Issues with planned maintenance date] > 0
      AND
      DateDiffWorkdays(
      [Measures].[Issue Planned maintenance date],
      [Measures].[Issue Real maintenance date]) >= 0
  )
)

best,
Gerda

3

Dear @gerda.grantina,
It is working perfectly fine now, a big thank you ! :slight_smile:

Have a nice day,

Antoine

1 Like