An example of Month to date calculation

Document created by antoniospeca Employee on Dec 12, 2017Last modified by ggallo on Dec 12, 2017
Version 3Show Document
  • View in full screen mode

When we have a cube by day, other than normal year to date we might want to know, given a day, the month to date value.

In the attached example I built this function using:

  1. A cube by day containing data
  2. A copy of this cube structured by Month and populated via dataflow with data from the previous cube
  3. A cube by Month containing 1 in Januarys and 0 on the other months

 

We can think month to date as: Year to date of the cube by day minus the year to date of the cube by month with offset-1 (that gives us the total of the year until the beginning of the month)

This logic is valid on every month except for January, where we simply have that Year to date is equal to month to date, then we need a cube to identify whether we are in Jan or not.

 

This logic can be used also in combination with Previous Year function to compare month to date across years.

13 people found this helpful

Attachments

Outcomes