Comparing Measures to previous months by day

[Jonny_Martin]

I am having an issue with building a report that compares the receipts received by the day with previous months. Since months have a different number of days the calculation is getting wonky. The 30th day in February is double adding the 29th. And we are missing the 31st day for January and March.

I want to be able to build a line graph similar to this. But the numbers aren’t calculating correctly

Any ideas?

[nauris.malitis]

Hi @Jonny_Martin

It’s important to reference the original measure, when calculating the cumulative sums of other months, and only after applying the CumulativeSum function.

For example, for “Cumulative Last Month”, you can use a formula like this:

Aggregate(
  CalculatedChildrenSet([Time].CurrentHierarchyMember),
  CumulativeSum(
    (
      [Measures].[Issues created], -- use the necessary measure here
      [Time].CurrentHierarchyMember.Level.DateMember(
        DateAdd('m', -1, [Time].CurrentHierarchyMember.MiddleDate)
      )
    )
  )
)

For the other months, change the value of “-1” to “-2”, “-3” etc.

​Best regards,
​Nauris

[Jonny_Martin]

@nauris.malitis Thanks for the Custom Measure!

I am still having the same issue. Here is the example: March has 31 days and February has 29days. When I use the Custom Measure to generate the result for February it tries to match the 31 Rows from March. Rows 30 and 31 are re adding the increase from row 29, so the amount is calculated 3 times.

How can we adjust the measure to only calculate for the days in the month and not the rows in the table?

[nauris.malitis]

Hi @Jonny_Martin

Thanks for the details!

Could you please share the formula for the measure that you are referencing in these formulas?

Thanks!
Nauris

[Jonny_Martin]

It is aan Imported Measure

[Measures].[Receipts ($)]

And then I used your formula

Aggregate(
  CalculatedChildrenSet([Time].CurrentHierarchyMember),
  CumulativeSum(
    (
      [Measures].[Receipts ($)], -- use the necessary measure here
      [Time].CurrentHierarchyMember.Level.DateMember(
        DateAdd('m', -1, [Time].CurrentHierarchyMember.MiddleDate)
      )
    )
  )
)

[nauris.malitis]

Hi @Jonny_Martin

Thanks for the details!

It looks like this part of the formula:

[Time].CurrentHierarchyMember.Level.DateMember(
  DateAdd('m', -1, [Time].CurrentHierarchyMember.MiddleDate)
)

Is returning Feb 29 for Mar 30 and Mar 31, so the value of Feb 29 is summed up several times at the end of the month.

Please try this formula that will check if the Month day (29) matches the month day of the previous month in Rows. If yes, then the formula will be executed; if not, then an empty value will be returned:

CASE WHEN
  [Time].CurrentHierarchyMember.GetNumber('Month day')
  =
  [Time].CurrentHierarchyMember.Level.DateMember(
    DateAdd('m', -1, [Time].CurrentHierarchyMember.MiddleDate)
  ).GetNumber('Month day')
THEN
  Aggregate(
    CalculatedChildrenSet([Time].CurrentHierarchyMember),
    CumulativeSum(
      (
        [Measures].[Receipts ($)], -- use the necessary measure here
        [Time].CurrentHierarchyMember.Level.DateMember(
          DateAdd('m', -1, [Time].CurrentHierarchyMember.MiddleDate)
        )
      )
    )
  )
END

Please note that these formulas work with the details visible in Rows, so if you select Feb in Rows with 29 days, the Previous month Jan will also be calculated for 29 days, and the 30th and 31st of Jan will be skipped since there are no Rows with these month days.

​Best regards,
​Nauris