Getting ready

To prepare for this recipe, do the following:

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

R06_Table = 
    VAR __Initialize = 
        SELECTCOLUMNS(
            {
                ( 0 , DATE(2012,1,1), DATE(2012,1,1), "DeptA" ),
                ( 0 , DATE(2012,1,1), DATE(2012,1,1), "DeptB" )
            },
            "Value",[Value1],
            "Hire Date",[Value2],
            "End Date",[Value3],
            "Department",[Value4]
        )
    VAR __Table = 
        ADDCOLUMNS(
            ADDCOLUMNS(
                GENERATESERIES(1,250,1),
                "Hire Date",RANDBETWEEN(DATE(2012,1,1),DATE(2012,6,30)) + DATE(1899,12,30)
            ),
            "End Date",
                IF([Value] < 80,
                    IF(MOD([Value],3) = 0,
                        BLANK(),
                        RANDBETWEEN([Hire Date]+180,DATE(2019,10,15)) + DATE(1899,12,30)
                    ),
                    IF(MOD([Value],11) = 0,
                        BLANK(),
                        RANDBETWEEN([Hire Date],DATE(2019,10,15)) + DATE(1899,12,30)
                    )
                ),
            "Department",IF([Value] < 80,"DeptA","DeptB")
        )
RETURN
    UNION(__Initialize, __Table)

3. Rename the Value column in the R06_Table table to Employee.

This data represents data on 250 unique employees and contains the date those employees were hired into the organization, Hire Date, left the organization, End Date, and their department, Department. This data has been specifically engineered to produce greater survivability in DeptA rather than DeptB.

It should be noted that, since this is a purely calculated table that uses random dates (RANDBETWEEN), the data in this table will change if a data refresh or reload event is triggered.