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:
- A cube by day containing data
- A copy of this cube structured by Month and populated via dataflow with data from the previous cube
- 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.