Calculate and Compare Actual VS Defined

Questions & Answers
, ,
1

Dear Team,

I have two date fields i.e Actual Kick Off Date and Kick Off Date. I need to create a report where it checks Kick Off Date meets Actual Kick Off and based on that ,if avg of meeting actual is 80% then mark column as green, if avg is 70% Mark it as amber.
Above result must be displayed in table according to this current year quarter wise. I am new to easy bi, it will be great help if anyone can help me find solution for above.

Thanks

2

Hi,

The first step is to import both date fields as measures: Jira custom fields

Once you do so, the following formula is for counting the issues with Kikckoff date in period and having the Actual kickoff date on the same day as Kickoff date:

NonZero(Count(
  Filter(Descendants([Issue].CurrentMember,[Issue].[Issue]),
  [Measures].[Issues with Kickoff date]>0
  AND
  Abs(DateDiffDays([Measures].[Issue Kickoff date],
    [Measures].[Issue Actual Kickoff date]
  ))<1
  )
))

You can divide it by the total issues with kickoff date to have the percentage:

NonZero(Count(
  Filter(Descendants([Issue].CurrentMember,[Issue].[Issue]),
  [Measures].[Issues with Kickoff date]>0
  AND
  Abs(DateDiffDays([Measures].[Issue Kickoff date],
    [Measures].[Issue Actual Kickoff date]
  ))<1
  )
))
/
[Measures].[Issues with Kickoff date]

Please, follow these guidelines to apply the cell formatting for your table report: Conditional cell formatting

Kindly,
Janis, eazyBI support

3

Hi Janis,

Thanks a lot, this was very useful and helped to achieve report as expected.

Thanks!

1 Like