Calculated Measure filter by date

Questions & Answers
1

Hi Team,

I’m trying to create a Year comparative report that looks like:

image
image2220×226 56.7 KB

That basically is counting the issues for that project accumulated until the date on the column,
For example:
Column ‘July’ is 10 because it gets the number from ‘June’ plus two more issues created.
It is worth mentioning that it doesn’t matter when the issue was created as soon as it fits in some criteria like status, project name, etc.
After the month passes to the next, the number should stay static.

I don’t have an idea if this is possible on EazyBI.

Any thoughts?

2

Hi @Daniel_Luevano

Here you could try using the new feature and add cumulative sum for your measure
https://docs.eazybi.com/eazybi/analyze-and-visualize/create-reports#Createreports-Addstandardcalculationsbasedonaselectedmeasure

Another alternative would be creating a new calculated measure using Ytd function.

Martins / eazyBI support

3

Thanks, Martins.
I tried with the cumulative sum, but when I expand the Time measure it keeps summing and it’s not accurate.

Looks like the YTD approach could work, I tried to do it for example with Feb 2019 with this formula:

    Sum(
Ytd([Time].[2019].[Q1 2019].[Feb 2019]),
[Measures].[Issues created count])

But it gave me 0 results.

Let me know if you need the report definition and I can mail it to you.

Daniel

4

Just put the time dimension in the rows and select the Month hierarchy and then from the column header select “Add Calculated” and Add cumulative calculation.

1 Like
5

Thanks Ran,
Didn’t work well.
Probably, I didn’t explain the requirement very well:
I basically need to create a measure that counts the issues created for example on Jan 2020 and keep that number on a static measure. Then create another for Feb that will include the ones in Jan and Feb, and so on.

Any ideas? An example maybe?

Thanks in advance.

Daniel

6

Hi Daniel,

Try this formula for Ytd calculation.
It counts the formula from the beginning of the particular year (based on the current member) until the member itself

SUM(
{
YTD([Time].CurrentMember)
},
[Measures].[Issues created count]
)

But YTD works only with the default hierarchy (it won’t work with weekly hierarchy).

Martins / eazyBI support

image
image2920×1350 265 KB