Hello All,

I am trying to recreate a dynamic date range for mutually exclusive measures in Wave. To compare, the equivalent in SQL would be referred to as a recursive CTE statement, see below. 

With Dates

    As (

    Select

        DATEADD(Yy, -2, DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0)) As Calendardate

    Union All

    Select

        DATEADD(Month, 1, Calendardate) As Calendardate

    From

        Dates

    Where 

         Calendardate<Getdate())

    Select 

        *

    From

        Dates;

This statement would return the last two years of month end periods, and I would use this to create a cube that can join in measures based on mutually exclusive dates to show unique instances period over period. For example, number of cases opened in that period, closed, or backlog at month end.

I need to do something similar in Wave, any advice?

CC:@Danielle Desrosiers