Getting ready

To prepare for this recipe, do the following:

1. Open Power BI Desktop.
2. Create a table called R05_Table using the following formula:

R05_Table = 
    VAR __Table1 = 
        GENERATE(
            SELECTCOLUMNS(GENERATESERIES(DATE(2019,1,15),DATE(2019,3,31),30),"Date",[Value]),
            SELECTCOLUMNS(GENERATESERIES(1,6,1),"Customer",[Value])
        )
    VAR __Table2 = 
        GENERATE(
            SELECTCOLUMNS(GENERATESERIES(DATE(2019,1,15),DATE(2019,2,28),30),"Date",[Value]),
            SELECTCOLUMNS(GENERATESERIES(7,9,1),"Customer",[Value])
        )
    VAR __Table3 = 
        GENERATE(
            SELECTCOLUMNS(GENERATESERIES(DATE(2019,1,15),DATE(2019,1,31),30),"Date",[Value]),
            SELECTCOLUMNS(GENERATESERIES(10,10,1),"Customer",[Value])
        )
    VAR __Table4 = 
        GENERATE(
            SELECTCOLUMNS(GENERATESERIES(DATE(2019,2,14),DATE(2019,3,31),30),"Date",[Value]),
            SELECTCOLUMNS(GENERATESERIES(11,11,1),"Customer",[Value])
        )
    VAR __Table5 = 
        GENERATE(
            SELECTCOLUMNS(GENERATESERIES(DATE(2019,3,16),DATE(2019,3,31),30),"Date",[Value]),
            SELECTCOLUMNS(GENERATESERIES(12,13,1),"Customer",[Value])
        )
RETURN
  UNION(__Table1, __Table2, __Table3, __Table4, __Table5)

3. Create columns in R05_Table using the following formulas:

Month = FORMAT([Date],"mmmm")
Month Sort = MONTH([Date])

4. Set the Sort by column for the Month column to the Month Sort column.

The data in R05_Table is representative of the data for a monthly subscription service and includes customers that fail to renew their monthly subscriptions as well as customers that add new subscriptions. Customers 1-6 start and remain loyal customers during the months of January, February, and March. Customers 7, 8, and 9 drop their subscriptions in March. Customer 10 drops their subscription in February. Customer 11 becomes a new customer in February and continues their subscription in March. Customers 12 and 13 both start new subscriptions in March.