Sum of measures by date (or period)

Questions & Answers
1

Hi all,

This my first time posting here and I’m not sure about the quality of this question…

Our issues contain a numeric field (Nb acts) that comes as a measure in EazyBI ( [Measures].[Nb acts]).
Each issue has a due date that is only calculted in EazyBI (for some reasons we can’t change this behavior right now). This date comes as another measure in EazyBI ([Measures].[Date Due]).

I need to know how to calculate, for a given time (either a date or a period), the number or acts (sum of [Measures].[Nb acts]) in unresolved issues depending on [Measures].[Date Due].

For example : how many acts are in the unresolved issues due for the first week of 2018.

I tried to play with dimensions and calculated measures and calculated members and didn’t find any way to achieve this simple task.
I come from an SQL background and I’m still trying to get my head around the MDX logic.

Best Regards

2

Hi Martin,

Could you let me know more details of the Date Due measure? Is it calculated by MDX in eazyBI or does it come in as date property from some other calculation?

I am thinking that the easiest way would be to import Date Due as measure and then Nb acts with Date due would be created as a measure grouping the numeric field by custom date field on Time. See more https://docs.eazybi.com/eazybijira/data-import/custom-fields#CustomFields-Measureswithcustomdatefields.

Lauma / support@eazybi.com

3

Thanks for answering !

Sadly, our “Date Due” is calculated by MDX (by adding a variable number of workdays to the creation date).
This field doesn’t exist before the data import so we can’t import it.

Regards

FFM

4

Ok, I see. In this case we need to use the custom MDX calculation that filters issues with the Date Due in selected time period. Please try the following formula

Sum(
  Filter(
    Descendants([Issue].CurrentMember,[Issue].[Issue]),
    -- if issue is created before the current time period
      DateBeforePeriodEnd(
        [Issue].CurrentHierarchyMember.get('Created at'),
        [Time].CurrentHierarchyMember
      ) -- Date due is in the period
    AND
    DateInPeriod(
      [Measures].[Date Due],
        [Time].CurrentHierarchyMember
      ) -- to match all other contexts of report
    AND ([Measures].[Issues created], [Time].CurrentHierarchy.DefaultMember) > 0),
  [Measures].[Nb acts]
)

Lauma / support@eazybi.com

1 Like
5

Thanks !

It worked !

Regards !

FFM

1 Like